### 1.4.11.2 The WHERE Clause

The WHERE clause is used to specify a selection criterion.

The syntax of the where clause is:

    SELECT column FROM table WHERE column operator value

With the WHERE clause, the following operators can be used:

    Operator    Description
    --------    -----------
    =           Equal
    <>          Not equal
    >           Greater than
    <           Less than
    >=          Greater than or equal
    <=          Less than or equal
    LIKE        Pattern match (described later)
    IS          Used for comparison to NULL
    IS NOT      Used for comparison to NULL

In some versions of SQL the `<>` operator may be written as `!=` but not in `web.database`. Note that the equals operator in SQL is `=` not `==` as it is in Python.

The `=` and `<>` operators cannot be used to compare `NULL` values because a field cannot be equal to nothing. Instead the `IS` and `IS NOT` operators should be used.

#### 1.4.11.2.1 Using the WHERE Clause

To select only the people whose last name are `Smith`, we add a WHERE clause to the SELECT statement:

    SELECT * FROM Person WHERE LastName='Smith'

Person table:

    +----------+-----------+--------+-------------+
    | LastName | FirstName | Number | DateOfBirth |
    +----------+-----------+--------+-------------+
    | 'Smith'  | 'John'    | 10     | 1980-01-01  |
    | 'Doe'    | 'John'    | 3      | 1981-12-25  |
    +----------+-----------+--------+-------------+

Result set:

    +----------+-----------+--------+-------------+
    | LastName | FirstName | Number | DateOfBirth |
    +----------+-----------+--------+-------------+
    | 'Smith'  | 'John'    | 10     | 1980-01-01  |
    +----------+-----------+--------+-------------+

#### 1.4.11.2.2 Using Quotes

Note that we have used single quotes around the conditional values in the examples.

SQL uses single quotes around text values (some database systems will also accept double quotes, not `web.database`). Numeric values should not be enclosed in quotes.

For text values:

This is correct:

    SELECT * FROM Person WHERE LastName='Smith'

This is wrong:

    SELECT * FROM Person WHERE LastName=Smith

For numeric values:

This is correct:

    SELECT * FROM Person WHERE Number>10

This is wrong:

    SELECT * FROM Person WHERE Number>'10'

#### 1.4.11.2.3 The LIKE Condition

The `LIKE` condition is used to specify a search for a pattern in a column.

    SELECT column FROM table WHERE column LIKE pattern

A `%` sign can be used to define wildcards (missing letters in the pattern).

The following SQL statement will return people with first names that start with an 'O':

    SELECT * FROM Person WHERE FirstName LIKE 'O%'

The following SQL statement will return people with first names that end with an 'a':

    SELECT column FROM table WHERE FirstName LIKE '%a'

The following SQL statement will return people with first names that contain the pattern 'la':

    SELECT column FROM table WHERE FirstName LIKE '%la%'

You can use as many `%` characters as you need in the pattern to match zero or more characters. If you need to have an actual `%` characters in the pattern you will need to escape it like this `
%`.

The following SQL statement will return values that end with a `%` character.

    SELECT column FROM table WHERE Percentage LIKE '%\%'

`web.database` does not support the `BETWEEN` condition since the same thing can be achieved using comparison operators.