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