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:
SELECT * FROM Student WHERE first_name = "Grigorij" AND YEAR(birthday) > 2000;
This code snippet uses two conditions:
- first_name = "Grigorij" — the student's first name must be "Grigorij"
- YEAR(birthday) > 2000 — birth year greater than 2000
Between them is the logical operator AND, which requires both conditions to be met simultaneously. As a result, we get only those students who meet both criteria.
Comparison operators
To compare values in SQL, special operators are used. They allow you to check whether values are equal, whether one is greater than another, whether they are not equal, and so on. The result of comparison can be:
- true (same as 1)
- false (same as 0)
- NULL (if the comparison result is undefined, for example, when comparing with NULL)
The result of comparing any value with NULL is NULL. The exception is the equivalence operator.
Try playing with these operators in the sandbox yourself and see what results they give:
SELECT 2 = 1, 'a' = 'a', 1 <=> NULL, NULL <=> NULL, 2 <> 2, 3 < 4, 10 <= 10, 7 > 1, 8 >= 10;
Logical operators
Logical operators are your helpers when you need to combine several conditions in one SQL query. With their help, you can flexibly select only those rows that you really need. Let's see how this works in practice:
-
AND — both conditions must be true.
Imagine you are looking for flights that simultaneously meet two requirements: for example, the aircraft must be of a certain model, and depart from a specific city. The AND operator helps combine these conditions.
SELECT * FROM Trip WHERE plane = 'Boeing' AND town_from = 'London';
This query will select only those flights where the aircraft model is Boeing and the departure city is London.
If at least one of the conditions is not met (for example, the aircraft is not Boeing or the departure is not from London), such a flight will not be included in the result.
-
OR — it's enough for at least one condition to be met.
The OR operator works as "or". If at least one of the conditions is true — the row will be included in the result. This is convenient when you want to see all flights that meet at least one of your criteria.
SELECT * FROM Trip WHERE town_to = 'Paris' OR plane = 'Airbus';
As a result, you will get all flights that arrive in Paris, as well as all flights on an Airbus aircraft (even if they don't fly to Paris).
If a flight is both on an Airbus and to Paris — it will also be included in the result.
-
NOT — the condition becomes opposite.
The NOT operator inverts the condition: if it was true, it becomes false, and vice versa. This is convenient when you want to exclude certain values.
SELECT * FROM Trip WHERE NOT town_to = 'Moscow';
This query will select all flights that arrive not in Moscow.
That is, if the arrival city is Moscow, such a flight will not be included in the result. Everything else — will be included.
-
XOR — this is an operator that helps select rows where only one of two conditions is met, but not both at the same time.
Suppose you want to find flights that either depart from Moscow or arrive in Paris, but not both options at the same time. Let's look at all possible cases:
SELECT * FROM trip WHERE town_from = 'Moscow' XOR town_to = 'Paris';
Note: the XOR operator is not available in all databases. If it's not available, you can use a combination of AND and OR.
Priority of logical operators
When you write a query with several conditions, SQL must understand in what order to check them. This is similar to mathematics: multiplication first, then addition. Logical operators in SQL also have their own order — this is called priority.
- First — NOT
- Then — AND
- Then — XOR
- Finally — OR
Why is this important?
It might seem that conditions will be checked simply from left to right, but this is not so! If you don't consider the order, you can get an unexpected result.
Let's look at such an example:
SELECT * FROM Trip WHERE town_to = 'Paris' OR plane = 'Boeing' AND NOT town_from = 'Moscow';
What happens here:
-
First, it checks that the flight is not from Moscow (NOT town_from = 'Moscow').
-
Then it checks that the aircraft is Boeing, and combines this with the first condition through AND, that is, it looks for flights that are not from Moscow and on Boeing (plane = 'Boeing' AND NOT town_from = 'Moscow')
-
Then all flights that arrive in Paris are added to the result, even if they are not on Boeing and depart from Moscow (town_to = 'Paris' OR ...).
As a result, you get the following selection:
- All flights that are not from Moscow and on Boeing
- Plus all flights that arrive in Paris
If you want to change the order of condition checking or make it clearer, use parentheses. Everything in parentheses is executed first. For example, if you manually place parentheses according to the priorities of logical operators, it immediately becomes clear how the query will be executed!
SELECT * FROM Trip WHERE ( town_to = 'Paris' OR ( plane = 'Boeing' AND (NOT town_from = 'Moscow') ) );
Now try it yourself: suppose you want to get a list of flights that depart from Paris or on a Boeing aircraft, and fly not to Moscow. This can be done by correctly placing parentheses in the original query from the example. How would you do it?