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;

id	first_name	middle_name	last_name	birthday	address
33	Grigorij	Gennadevich	Kapustin	2001-12-13T00:00:00.000Z	ul. Pervomajskaya, d. 45, kv. 6
65	Grigorij	Kirillovich	Kolobov	2003-07-17T00:00:00.000Z	ul. CHernova, d. 9, kv. 34

This code snippet uses:

2 comparison operators first_name = "Grigorij" and YEAR(birthday) > 2000;
1 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.

Operator	Description
=	Equality operator
<=>	Equality operator
<> or !=	Inequality operator
<	Less than operator
<=	Less than or equal to operator
>	Greater than operator
>=	Greater than or equal to operator

Logical Operators

Logical operators are necessary for linking comparison operators.

Operator	Description
NOT	Changes the value of the comparison operator to the opposite
OR	Returns the common value of the expression to be true if at least one of them is true
AND	Returns the common value of the expression to be true if they are both true
XOR	Returns the common value of the expression to be true if one and only one argument is true

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';