The situation where a selection needs to be made based on a specific condition is very common. For this, the WHERE operator exists in the SELECT statement, which is followed by conditions for limiting rows. If a record satisfies this condition, it appears in the result; otherwise, it is discarded.
SELECT [DISTINCT] table_fields FROM table_name WHERE row_limit_conditions [logical_operator other_row_limit_conditions];
For example, a query using the WHERE operator may look like this:
SELECT * FROM Student WHERE first_name = "Grigorij" AND YEAR(birthday) > 2000;
This code snippet uses:
- two comparison operators first_name = "Grigorij" and YEAR(birthday) > 2000;
- one logical operator AND
As a result, we obtain data on students whose first name is "Grigorij" and whose birth year is greater than 2000.
Comparison operators are used to compare 2 expressions, the result of which can be:
- true (equivalent to 1)
- false (equivalent to 0)
The result of comparison with NULL is NULL. The exception is the equivalence operator.
Try to run the query and play with these operators in the sandbox yourself:
SELECT 2 = 1, 'a' = 'a', NULL <=> NULL, 2 <> 2, 3 < 4, 10 <= 10, 7 > 1, 8 >= 10;
Logical operators are necessary for linking comparison operators.
Let's take for example, outputting all flights that were made on a "Boeing" plane, but the departure was not from London:
SELECT * FROM Trip WHERE plane = 'Boeing' AND NOT town_from = 'London';