Conditional operator WHERE

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.

General structure of a query with the WHERE operator

MySQL
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:

MySQL
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

Comparison operators are used to compare 2 expressions, the result of which can be:

• true (equivalent to 1)
• false (equivalent to 0)
• NULL

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:

MySQL
SELECT
    2 = 1,
	'a' = 'a',
	NULL <=> NULL,
	2 <> 2,
	3 < 4,
	10 <= 10,
	7 > 1,
	8 >= 10;

Logical Operators

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:

MySQL
SELECT * FROM Trip
WHERE plane = 'Boeing' AND NOT town_from = 'London';