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 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.
|=||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
|The inequality operator|
|<=||Less than or equal to operator|
|>=||Greater than or equal to operator|
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 member_name FROM FamilyMembers WHERE member_name LIKE '% Quincey';
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.
|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".|
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 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';