Filtering with IN and BETWEEN

SQL offers specialized operators to enhance data filtering. When conditions require checking if a value belongs to a specific set of options or falls within a continuous range, the IN and BETWEEN operators are available. These operators provide a more concise and readable way to perform such filtering tasks.

Checking Against a List of Values with IN

Imagine you want to retrieve data for rows where a specific column matches one of several possible values. For example, you might want to find all customers living in 'California', 'Nevada', or 'Arizona'. You could write this using multiple OR conditions:

SELECT customer_name, state
FROM customers
WHERE state = 'California' OR state = 'Nevada' OR state = 'Arizona';

This works, but it can become lengthy and harder to read if your list of values grows. The IN operator provides a cleaner way to express the same logic. It allows you to specify a list of values to check against.

The basic syntax is:

WHERE column_name IN (value1, value2, value3, ...)

Using IN, the previous query becomes much more compact:

SELECT customer_name, state
FROM customers
WHERE state IN ('California', 'Nevada', 'Arizona');

This query retrieves the exact same results as the one using OR, but it's generally easier to write and understand, especially with longer lists. The IN operator works with various data types, including numbers and strings.

Here's another example, selecting orders with specific status codes:

SELECT order_id, order_date, status_code
FROM orders
WHERE status_code IN (100, 205, 410);

You can also combine IN with other conditions using AND or OR. For instance, to find customers in specific states and who signed up after a certain date:

SELECT customer_name, state, signup_date
FROM customers
WHERE state IN ('California', 'Nevada', 'Arizona')
  AND signup_date > '2023-01-01';

Just like you can use NOT with other operators, you can use NOT IN to find rows where the column's value does not match any value in the list:

SELECT customer_name, state
FROM customers
WHERE state NOT IN ('California', 'Nevada', 'Arizona');

This would return customers from all states except California, Nevada, and Arizona.

Checking Within a Range with BETWEEN

Another common filtering scenario involves selecting rows where a column's value falls within a specific range, including the boundaries. For instance, finding products with a price between $50 and$100 (inclusive). You could use AND with comparison operators:

SELECT product_name, price
FROM products
WHERE price >= 50 AND price <= 100;

Again, this works perfectly well. However, SQL offers the BETWEEN operator as a more direct way to express ranges.

The syntax is:

WHERE column_name BETWEEN lower_bound AND upper_bound

An important detail is that BETWEEN is inclusive: it includes rows where the column value is equal to lower_bound or upper_bound.

Using BETWEEN, the product price query looks like this:

SELECT product_name, price
FROM products
WHERE price BETWEEN 50 AND 100;

This is often considered more readable for expressing ranges. BETWEEN is commonly used with numbers and dates.

Example with dates (syntax might vary slightly depending on the specific SQL database):

SELECT event_name, event_date
FROM events
WHERE event_date BETWEEN '2024-06-01' AND '2024-06-30';

This query selects events that occurred in June 2024, including those on the 1st and the 30th.

Similar to IN, you can use NOT BETWEEN to exclude a range:

SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 50 AND 100;

This retrieves products with prices less than $50 or greater than$100.

Both IN and BETWEEN simplify common filtering tasks. They make your WHERE clauses shorter and often easier to interpret compared to using multiple OR conditions or compound inequalities with AND. Mastering their use is a useful step in writing effective SQL queries.