SQL | BETWEEN & IN Operator

In SQL, the BETWEEN and IN operators are used to filter data based on a range of values or a list of values, respectively. Let's dive into each one of them.

1. BETWEEN Operator:

The BETWEEN operator is used to filter the result set within a specific range. It can be used with numbers, text, and even dates.

Syntax:

column_name BETWEEN value1 AND value2;

Example:

Suppose you want to get all products from a products table priced between $10 and $50:

SELECT * FROM products WHERE price BETWEEN 10 AND 50;

For dates, consider a sales table and you want to get all sales between January 1, 2022, and December 31, 2022:

SELECT * FROM sales WHERE sale_date BETWEEN '2022-01-01' AND '2022-12-31';

Note: The values defined in the BETWEEN operator are inclusive. This means that the boundary values (value1 and value2) are included in the result set.

2. IN Operator:

The IN operator is used to filter the result set based on a list of values.

Syntax:

column_name IN (value1, value2, ...);

Example:

Suppose you want to get all products from a products table that belong to category 1, 3, or 5:

SELECT * FROM products WHERE category_id IN (1, 3, 5);

Another example is retrieving employees from a employees table with the names 'John', 'Jane', or 'Doe':

SELECT * FROM employees WHERE first_name IN ('John', 'Jane', 'Doe');

Using NOT:

Both BETWEEN and IN operators can be combined with the NOT operator to retrieve records that don't match the specified criteria.

Example:

To get products not in category 1, 3, or 5:

SELECT * FROM products WHERE category_id NOT IN (1, 3, 5);

To get sales outside of the year 2022:

SELECT * FROM sales WHERE sale_date NOT BETWEEN '2022-01-01' AND '2022-12-31';

Points to Note:

Ensure that the data types of the values in your conditions match the data type of the columns you're comparing against.
Using large lists with the IN operator can be less efficient than other filtering methods, especially with very large databases.
When working with strings, especially dates, ensure that you're using the correct format expected by your database system.
While the BETWEEN operator is inclusive, always double-check if the boundary values are part of your intended criteria.

Using BETWEEN Operator in SQL Examples:

SELECT *
FROM your_table
WHERE column1 BETWEEN 10 AND 20;

How to use BETWEEN and IN Operators in a Single Query:

SELECT *
FROM your_table
WHERE column1 BETWEEN 10 AND 20
AND column2 IN ('A', 'B', 'C');

Range Filtering with BETWEEN in SQL:

SELECT *
FROM your_table
WHERE column1 BETWEEN 1000 AND 2000;

Filtering Values using IN Operator in SQL:

SELECT *
FROM your_table
WHERE column1 IN (10, 15, 20);

Combining BETWEEN and IN Operators in SQL Queries:

SELECT *
FROM your_table
WHERE column1 BETWEEN 10 AND 20
AND column2 IN ('A', 'B', 'C');

Advanced Examples of BETWEEN and IN Operators in SQL:

SELECT *
FROM your_table
WHERE (column1 BETWEEN 100 AND 200 OR column2 = 'X')
AND column3 IN ('P', 'Q', 'R');

SQL NOT BETWEEN and NOT IN Operators:

SELECT *
FROM your_table
WHERE column1 NOT BETWEEN 10 AND 20
AND column2 NOT IN ('A', 'B', 'C');

Inclusive vs Exclusive Range with BETWEEN in SQL:

SELECT *
FROM your_table
WHERE column1 BETWEEN 10 AND 20; -- Inclusive range

SELECT *
FROM your_table
WHERE column1 > 10 AND column1 < 20; -- Exclusive range