SQLite: the WHERE clause


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.

Crash Course Delphi  Database tutorials  FAQ  Tips  Source Code  Downloads  Links