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

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:

WHERE first_name = "Grigorij" AND YEAR(birthday) > 2000;
33GrigorijGennadevichKapustin2001-12-13T00:00:00.000Zul. Pervomajskaya, d. 45, kv. 6
65GrigorijKirillovichKolobov2003-07-17T00:00:00.000Zul. 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.

=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.

NOTChanges the value of the comparison operator to the opposite
ORReturns the common value of the expression to be true if at least one of them is true
ANDReturns the common value of the expression to be true if they are both true
XORReturns 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:

WHERE plane = 'Boeing' AND NOT town_from = 'London';