SQL | BETWEEN & IN Operator

The BETWEEN and IN operators are both used in SQL to filter results, but they work in different ways. Here’s a breakdown of each:

1. BETWEEN Operator

The BETWEEN operator is used to filter the result set within a certain range. The range can be numbers, text, or dates. It includes the boundary values.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• value1 and value2 define the range.
• The range is inclusive, meaning it includes both value1 and value2.

Example:

SELECT * 
FROM employees
WHERE salary BETWEEN 3000 AND 5000;

This query retrieves employees with salaries between 3000 and 5000 (inclusive).

Important Notes:

• For numbers and dates, BETWEEN works by considering the lower value first and then the higher value (order matters).
• If you use BETWEEN for dates, ensure the format of the date is consistent with your database (e.g., YYYY-MM-DD).

2. IN Operator

The IN operator is used to check if a value matches any value in a list. This is often more readable than using multiple OR conditions.

Syntax:

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

• The list of values can be numbers, strings, or even dates.

Example:

SELECT * 
FROM employees
WHERE department IN ('Sales', 'Marketing', 'HR');

This query retrieves employees who belong to the “Sales”, “Marketing”, or “HR” departments.

Important Notes:

• You can use IN with subqueries as well.
• IN makes the query more efficient than using multiple OR conditions for large lists.

Key Differences:

• BETWEEN is used for a range of values, whereas IN is used for specific values.
• BETWEEN is inclusive of the boundary values, while IN checks for exact matches in the list.