SQLite's WHERE clause is used to specify a condition when fetching data from a table.
Syntax: SELECT columns FROM tablename WHERE condition
You use the WHERE clause to filter the records, fetching only necessary records.
You specify a condition using comparision or logical operators such as >, <, =, !=, >=, <=,
<>, LIKE, NOT, IN, etc. So the syntax becomes:
SELECT columns FROM tablename WHERE field1 operator1 operand1
[AND / OR field2 operator2 operand2] [AND / OR more conditions]
Example:
Using the table Countries from our previous tutorial, the following SELECT statement lists all the records
where field population is greater than 10000 AND smaller than 20000:
SELECT * FROM countries WHERE population > 10000 AND population < 20000
SQLite Comparison Operators
Operator |
Description |
Example: WHERE... |
= |
Checks if the value of the field is equal to the operand. If equal, then the condition is true. |
curcode = "USD" |
!= |
Checks if the value of the field is NOT equal to the operand. If not equal, then the condition is true. |
curcode != "USD" |
> |
Checks if the value of the field is greater than the operand. |
population > 10000 |
< |
Checks if the value of the field is smaller than the operand. |
population < 10000 |
SQLite Logical Operators
Operator |
Description |
Example: WHERE... |
AND |
Combines multiple conditions in a WHERE clause. |
curcode = "USD" AND population < 100000 |
OR |
Combines multiple conditions in a WHERE clause. |
curcode = "USD" OR curcode = "AUD" |
BETWEEN |
Search for values that are within a set of values, given the minimum value and the maximum value. |
population BETWEEN 10000 AND 20000 |
IN |
Compare to a list of literal values. |
curcode in ("USD", "AUD") |
LIKE |
Compare to similar values using wildcard operators.
Percent sign (%) represents 0, 1 or more characters. Underscore (_) represents a single character.
Examples: value starting with "a"
value containing "au"
value containing "on" and ending in "s"
value with "au" starting in second position |
name LIKE "a%" name LIKE "%au%" name LIKE "%on%s" name LIKE "_au%" |
IS |
Works like = |
|
NOT |
The "negate" operator. Reverses the meaning of the logical operator with which it is used. Eg. NOT LIKE, NOT IN, etc. |
|
|
|