Conditional operator WHERE

The situation when you want to make a selection according to a certain condition is very common. To do this, there is a WHERE parameter in the SELECT statement, followed by a condition for restricting rows. If the record satisfies this condition, it falls into the result, otherwise it is discarded.

General query structure with the WHERE operator

MySQL

SELECT table_fields FROM list_of_tables 
WHERE the_conditions_of_the_limitations
[logical_operator the_other_conditions_of_the_limitations];

In the described query structure, optional parameters are indicated in square brackets.

The conditional operator uses comparison operators, special operators, and logical operators.

Comparison operators

Comparison operators are used to compare 2 expressions, their result can be TRUE (1), FALSE (0) and NULL.

Result of comparison with NULL is NULL. The exception is the equivalence operator.

Operator	Description
=	The equality operator
<=>	The operator equivalence. Similar to the equality operator, with only one exception: in contrast, the equivalence operator will return TRUE when comparing NULL <=> NULL
<> or !=	The inequality operator
<	Less operator
<=	Less than or equal to operator
>	Greater operator
>=	Greater than or equal to operator

Special operators

1. IS [NOT] NULL — allows you to find out if the checked value is NULL.

As an example, we derive all family members whose family status is not NULL:

MySQL

SELECT * FROM FamilyMembers
WHERE status IS NOT NULL;

2. [NOT] BETWEEN min AND max — allows you to find out if the checked column value is located between min and max.

Display all the data on purchases with a price from 100 to 500 from the Payments table:

MySQL

SELECT * FROM Payments
WHERE unit_price BETWEEN 100 AND 500;

3. [NOT] IN — allows you to find out whether the checked value of a column is included in the list of defined values.

Print the names of family members whose status is "father" or "mother":

MySQL

SELECT member_name FROM FamilyMembers
WHERE status IN ('father', 'mother');

4. [NOT] LIKE pattern [ESCAPE symbol]— allows you to find out if a string matches a specific pattern.

For example, output all people with the surname "Quincey":

MySQL

SELECT member_name FROM FamilyMembers
WHERE member_name LIKE '% Quincey';

Wildcard symbols

The template is allowed to use two wildcard symbols:

the underscore symbol (_), which can be used instead of any single character in the checked value
the percent symbol (%) replaces the sequence of any characters (the number of characters in the sequence can be from 0 or more) in the checked value.

Pattern	Description
never%	Matches any line starting with "never".
%ing	Matches any line ending with «ing».
_ing	Matches strings with a length of 4 characters, while the last 3 must be "ing". For example, the words "sing" and "wing".

ESCAPE symbol

The ESCAPE symbol is used to escape the wildcard characters. In case you need to find strings containing percentages (and percent is a reserved character), you can use ESCAPE symbol.

For example, you want to get identifiers for tasks whose progress is 3%:

MySQL

SELECT job_id FROM Jobs
WHERE progress LIKE '3!%' ESCAPE '!';

If we had not screened the wildcard character, then everything that starts at 3 would fall into the selection.

Logical operators

Logical operators are required for string constraint condition relationships.

Operator NOT — changes the value of the special operator to the opposite
Operator OR — the general meaning of the expression is true if at least one of them is true
Operator AND — the general meaning of an expression is true if they are both true
Operator XOR — the general meaning of the expression is true if one and only one argument is true

We will derive all the flights that were completed on a Boeing aircraft, but at the same time, the flight was not from London:

MySQL

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