Filtering results

You can filter the result by specifying a filter condition in the WHERE clause. Typically, a filter condition is an expression that consists of one or more comparison expressions connected through the logical operators AND and/or OR. The comparison operators: =, !=, >, >=, <, and <= are supported. For example:

To return users whose firstname 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 the 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 the users whose age is between 30 and 40 or their 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 operator to show rows where a field exists in the table, but 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 also use the IS NOT NULL operator to show rows where an existing field contains 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