Advanced Filtering: IN, BETWEEN, and IS NULL

Definition: These three operators are "shorthand" tools that make your SQL queries more readable and powerful. They allow you to handle multiple values, numerical ranges, and empty data fields without writing long, repetitive code.

Why: Beginner SQL curriculums include these filters because they simplify complex logic. Instead of linking multiple OR statements together, you can use these operators to write cleaner, professional-grade queries.

1. The IN Operator

Use IN when you want to compare a column against a list of multiple possible values. It acts like a multiple-choice filter.

SELECT *
FROM students
WHERE city IN ('Mysuru', 'Hassan');

This returns students from either Mysuru OR Hassan.

2. The BETWEEN Operator

Use BETWEEN to select values within a specific range. It is inclusive, meaning it includes the start and end values.

SELECT *
FROM students
WHERE age BETWEEN 18 AND 20;

This returns students who are exactly 18, 19, or 20 years old.

3. The IS NULL Operator

In databases, an empty field is called NULL. You cannot use = NULL; instead, you must use IS NULL to find missing data.

SELECT *
FROM students
WHERE city IS NULL;

This returns students who do not have a city listed in their record.

Summary Table

Key Notes

• Negating Filters: You can use NOT with all of these (e.g., NOT IN, NOT BETWEEN, or IS NOT NULL) to find the opposite results.
• Between Dates: BETWEEN is extremely useful for dates, such as WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'.
• NULL is not Zero: Remember that NULL is the absence of a value. A city name that is an empty string ('') or a numeric value of 0 is not the same as NULL.