We have already familiarized ourselves with the syntax of the WHERE operator and comparison operators, but in addition to them, we can use the following useful operators in conditional queries:
- IS NULL
Let's look at their application.
The IS NULL operator allows you to find out if the checked value is equal to NULL, i.e. if the value is empty.
For example, let's select all teachers who do not have a middle name:
SELECT * FROM Teacher WHERE middle_name IS NULL;
To use negation, i.e. if we want to find all records where the field is not equal to NULL, we must use the following syntax:
SELECT * FROM Teacher WHERE middle_name IS NOT NULL;
The BETWEEN min AND max operator allows you to find out if the checked value of the column is located in the interval between min and max, including the values min and max themselves.
It is identical to the condition:
... WHERE field >= min AND field <= max
This operator is used as follows:
SELECT * FROM Payments WHERE unit_price BETWEEN 100 AND 500;
All records from the Payments table where the value of the unit_price field is from 100 to 500 will be returned as a result.
The IN operator allows you to find out if the checked value of the column is included in a list of certain values.
SELECT * FROM FamilyMembers WHERE status IN ('father', 'mother');