IN and BETWEEN Operators – SQL Tutorial for Beginners

The IN and BETWEEN operators are essential tools in database querying that help simplify complex conditions and improve readability. While the IN operator is used to check whether a value exists within a list of specific values, the BETWEEN operator is used to determine whether a value falls within a defined range.

In SQL, these two operators are often combined to create powerful queries that filter data based on both multiple categories and range conditions at the same time. This combination reduces the need for lengthy conditions using multiple OR and AND statements.

For example, you can easily retrieve records for selected departments using the IN operator while simultaneously filtering results within a certain salary or date range using the BETWEEN operator.

Overall, combining IN and BETWEEN operators allows developers and analysts to write clean, efficient, and easy-to-maintain queries, making them highly useful in real-world applications like reporting, analytics, and data filtering.

The IN and BETWEEN operators in SQL help you filter data efficiently based on multiple values or a specific range. These operators make your queries cleaner, faster, and easier to understand.

In this SQL tutorial, you will learn:

• What IN operator is

• What BETWEEN operator is

• Syntax and usage

• Real-world examples

• Best practices

What Is the IN Operator?

The IN operator is a commonly used feature in programming and database languages that helps you check whether a specific value exists within a group of values. Instead of writing multiple conditions, the IN operator allows you to perform the same task in a simpler and more efficient way.

In languages like SQL, Python, and JavaScript, the IN operator plays an important role in filtering data, checking membership, and improving code readability.

For example, rather than checking a value against multiple conditions using OR, the IN operator lets you define a list of values in a single statement. This not only reduces code complexity but also makes your logic easier to understand and maintain.

The IN operator allows you to filter data by matching a value against multiple possible values.

Use Case

Instead of writing:

WHERE city = 'Delhi' OR city = 'Mumbai' OR city = 'Pune'

You can simply use:

WHERE city IN ('Delhi', 'Mumbai', 'Pune')

Syntax

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

IN Operator Examples

Example 1: Filter employees from specific cities

SELECT * FROM employees
WHERE city IN ('Delhi', 'Mumbai', 'Bengaluru');

Example 2: Filter products by category

SELECT * FROM products
WHERE category IN ('Electronics', 'Mobile', 'Laptop');

Example 3: Exclude values using NOT IN

SELECT * FROM users
WHERE id NOT IN (1, 3, 7);

What Is the BETWEEN Operator?

The BETWEEN operator is used in programming and database queries to filter values that fall within a specific range. It allows you to check whether a value lies between two defined limits, making your conditions more clear, concise, and readable.

In SQL, the BETWEEN operator is widely used to retrieve data within a range of numbers, dates, or even text values. Instead of writing multiple conditions using comparison operators, BETWEEN simplifies the logic into a single, easy-to-understand statement.

For example, when working with datasets such as salaries, dates, or scores, the BETWEEN operator helps you quickly extract values that lie within a desired range. This improves both query efficiency and code maintainability.

The BETWEEN operator is used to filter data within a range of values.

Works With

• Numbers

• Dates

• Text (alphabetical range)

Syntax

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

BETWEEN Operator Examples

Example 1: Salary range

SELECT * FROM employees
WHERE salary BETWEEN 30000 AND 60000;

Example 2: Date range

SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

Example 3: Text range

SELECT * FROM students
WHERE name BETWEEN 'A' AND 'M';

Example 4: NOT BETWEEN

SELECT * FROM sales
WHERE amount NOT BETWEEN 500 AND 1000;

Combining IN and BETWEEN

In this example, the IN operator is used to select products that belong to specific categories — 'Mobile' and 'Laptop'. At the same time, the BETWEEN operator is used to limit the results to products whose price falls within the range of 20,000 to 50,000. Since BETWEEN is inclusive, it includes both 20,000 and 50,000 in the result.

So, this query will return only those products that match both conditions:

• The product must be in either the Mobile or Laptop category
• AND the price must be within the specified range

Overall, combining these operators helps you retrieve precise and relevant data while keeping the query clean and easy to understand.

You can use both operators together.

Example: Products in category + price range

SELECT * FROM products
WHERE category IN ('Mobile', 'Laptop')
AND price BETWEEN 20000 AND 50000;

Real-World Use Cases

 Filtering Users Between Age Groups

SELECT * FROM users
WHERE age BETWEEN 18 AND 30;

Finding Orders From Specific Statuses

SELECT * FROM orders
WHERE status IN ('Pending', 'Shipped');

Sales Report for a Quarter

SELECT * FROM sales
WHERE sale_date BETWEEN '2024-04-01' AND '2024-06-30';

Best Practices

✔ Use IN for matching multiple fixed values
✔ Use BETWEEN for numeric/date ranges
✔ Always verify date formats (YYYY-MM-DD)
✔ Use parentheses when combining with AND/OR
✔ Avoid NOT IN with NULL values (use NOT EXISTS instead)

Summary

In this tutorial, you learned:

• How to use the IN operator to match multiple values

• How to use the BETWEEN operator to filter ranges

• Real-world examples for both operators

• Best practices for writing clean SQL queries

IN and BETWEEN make SQL filtering simpler, faster, and more readable.