Filtering Results

You can filter query results by specifying a filter condition in the WHERE clause. Typically, a filter condition consists of one or more comparison expressions connected through logical operators AND or OR. The comparison operators are also supported: =, !=, >, >=, <, and <= .

This query filters results to return only users whose first name is John:

sql-> SELECT id, firstname, lastname FROM Users WHERE firstname = "John";
 +----+-----------+----------+
 | id | firstname | lastname |
 +----+-----------+----------+
 |  3 | John      | Morgan   |
 |  2 | John      | Anderson |
 +----+-----------+----------+

2 rows returned 

To return users whose calculated monthlysalary is greater than 6000:

sql-> SELECT id, lastname, income, income/12 AS monthlysalary
FROM Users WHERE income/12 > 6000;
 +----+----------+--------+---------------+
 | id | lastname | income | monthlysalary |
 +----+----------+--------+---------------+
 |  5 | Scully   | 400000 |         33333 |
 |  4 | Smith    |  80000 |          6666 |
 |  2 | Anderson | 100000 |          8333 |
 |  1 | Morrison | 100000 |          8333 |
 +----+----------+--------+---------------+

5 rows returned 

To return users whose age is between 30 and 40 or whose income is greater than 100,000:

sql-> SELECT lastname, age, income FROM Users 
WHERE age >= 30 and age <= 40 or income > 100000;
 +----------+-----+--------+
 | lastname | age | income |
 +----------+-----+--------+
 | Smith    |  38 |  80000 |
 | Morgan   |  38 |   NULL |
 | Anderson |  35 | 100000 |
 | Scully   |  47 | 400000 |
 +----------+-----+--------+

4 rows returned 

You can use parenthesized expressions to alter the default precedence among operators. For example:

To return the users whose age is greater than 40 and either their age is less than 30 or their income is greater or equal than 100,000:

sql-> SELECT id, lastName FROM Users WHERE
(income >= 100000 or age < 30) and age > 40;
 +----+----------+
 | id | lastName |
 +----+----------+
 |  5 | Scully   |
 +----+----------+

1 row returned 

You can use the IS NULL condition to return results where a field column value is set to SQL NULL (SQL NULL is used when a non-JSON field is set to null):

sql-> SELECT id, lastname from Users WHERE income IS NULL;
 +----+----------+
 | id | lastname |
 +----+----------+
 |  3 | Morgan   |
 +----+----------+

1 row returned 

You can use the IS NOT NULL condition to return column values that contain non-null data:

sql-> SELECT id, lastname from Users WHERE income IS NOT NULL;
 +----+----------+
 | id | lastname |
 +----+----------+
 |  4 | Smith    |
 |  1 | Morrison |
 |  5 | Scully   |
 |  2 | Anderson |
 +----+----------+

4 rows returned