SQL IN and BETWEEN Operators

The IN and BETWEEN operators are shorthand ways to write multiple conditions in a WHERE clause. They make queries cleaner, easier to read, and less repetitive compared to chaining multiple OR conditions.

The Reference Table

The IN Operator

The IN operator checks whether a column's value matches any value in a given list. It is a cleaner alternative to writing multiple OR conditions.

Syntax

SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, value3, ...);

Without IN (Using OR)

SELECT * FROM Students
WHERE City = 'Delhi' OR City = 'Mumbai' OR City = 'Pune';

With IN (Cleaner Approach)

SELECT * FROM Students
WHERE City IN ('Delhi', 'Mumbai', 'Pune');

Both queries return the same result, but IN is shorter and easier to read.

Result:

IN With Numeric Values

SELECT * FROM Students
WHERE Age IN (19, 21, 23);

Result: Returns Arjun Nair (19), Sneha Kapoor (21), and Rohit Das (23).

NOT IN

NOT IN returns all rows where the column value does not match any value in the list.

SELECT * FROM Students
WHERE City NOT IN ('Delhi', 'Mumbai');

Result: Arjun Nair (Chennai), Sneha Kapoor (Pune), Rohit Das (Kolkata) — all students not from Delhi or Mumbai.

The BETWEEN Operator

The BETWEEN operator selects rows where a column's value falls within a specified range. It is inclusive — meaning both the start and end values are included in the result.

Syntax

SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

BETWEEN With Numbers

SELECT * FROM Students
WHERE Age BETWEEN 19 AND 21;

Result: All students whose age is 19, 20, or 21 (inclusive).

BETWEEN With Decimal Values (Fees)

SELECT StudentName, Fees FROM Students
WHERE Fees BETWEEN 40000 AND 55000;

Result: Ravi Sharma (45000), Priya Mehta (52000), Sneha Kapoor (47000) — all within the fee range.

BETWEEN With Dates

SELECT * FROM Students
WHERE JoinDate BETWEEN '2024-01-01' AND '2024-02-28';

This returns all students who joined between January 1 and February 28, 2024 — both dates included.

NOT BETWEEN

NOT BETWEEN returns rows where the value falls outside the specified range.

SELECT StudentName, Fees FROM Students
WHERE Fees NOT BETWEEN 40000 AND 55000;

Result: Arjun Nair (38000 — below the range) and Rohit Das (60000 — above the range).

Comparing IN vs BETWEEN

Key Points to Remember

• IN is best when checking a column against a specific list of known values.
• BETWEEN is best when checking a column against a range — numbers, dates, or even text.
• Both BETWEEN endpoints are inclusive — the boundary values are included in the result.
• Both operators have a NOT version: NOT IN and NOT BETWEEN.
• IN can be combined with a subquery to compare against dynamic data — this is covered in the SQL Subqueries topic.

Summary

The IN operator simplifies multiple OR conditions by checking a column against a list of values. The BETWEEN operator simplifies range comparisons by checking if a value falls between two boundaries. Both make SQL queries shorter, cleaner, and easier to understand.