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;
idfirst_namemiddle_namelast_namebirthdayaddress
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:

  • 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
OperatorSignDescription
Equality=If both values are equal, then the result will be 1, otherwise 0
Equivalence<=>It is similar to the equality operator, except that the result will be 1 in the case of comparing NULL with NULL and 0 when comparing any value with NULL
Inequality<> or !=If both values are not equal, then the result will be 1, otherwise 0
Less than<If one value is less than the other, the result will be 1, otherwise 0
Less than or equal<=If one value is less than or equal to the other, the result will be 1, otherwise 0
Greater than>If one value is greater than the other, the result will be 1, otherwise 0
Greater than or equal>=If one value is greater than or equal to the other, the result will be 1, otherwise 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:

MySQL
SELECT
    2 = 1,
	'a' = 'a',
	NULL <=> NULL,
	2 <> 2,
	3 < 4,
	10 <= 10,
	7 > 1,
	8 >= 10;
2 = 1'a' = 'a'NULL <=> NULL2 <> 23 < 410 <= 107 > 18 >= 10
01101110

Logical Operators

Logical operators are necessary for linking comparison operators.

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

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