IS NULL, BETWEEN, IN Operators

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
• BETWEEN
• IN

Let's look at their application.

IS NULL

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:

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

MySQL
SELECT * FROM Teacher
WHERE middle_name IS NOT NULL;

BETWEEN

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:

MySQL
... WHERE field >= min AND field <= max

This operator is used as follows:

MySQL
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.

IN

The IN operator allows you to find out if the checked value of the column is included in a list of certain values.

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