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

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.

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:

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:

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

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

SELECT name FROM FamilyMembers
WHERE 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.

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

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.

• Оператор NOT — changes the value of the special operator to the opposite
• Оператор OR — the general meaning of the expression is true if at least one of them is true
• Оператор AND — the general meaning of an expression is true if they are both true
• Оператор 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:

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