How to do select from where x is equal to multiple values?

sql

subqueries

join

performance

byAnton Shumikhin·Dec 19, 2024

When you need to check if a column equals multiple values, the IN clause is your best bet. It effectively simplifies your SQL statements, bundling multiple OR conditions into one.

Consider this: You need to fetch records from the products table where product_id is either 3, 7, or 15. The IN clause makes it simple:

SELECT *
FROM products
WHERE product_id IN (3, 7, 15);  -- Someone needs stuff with ID 3, 7 and 15. Maybe for a secret ritual? 🌚

This format significantly enhances the readability of the query and makes complex SQL more manageable.

Replacing long "OR" chains

An uncomfortable string of OR conditions is the stuff of nightmares for a DBA. Not only does it muddle your queries, but it can also lead to performance issues.

When you're checking a column against multiple values, reduce complexity by using the IN clause and sleeping peacefully at night.

Mastering conditions with parentheses

Conditions in SQL can quickly morph into a complex web of logic. This is particularly true when a statement involves AND and OR. Here's where parentheses prove to be lifesavers - they enclose conditions, ensuring logical operators perform in the right order.

SELECT *
FROM orders
WHERE customer_id = 101 AND (status = 'Shipped' OR status = 'Delivered');

Without parentheses, we might have shipped and delivered all orders to a customer who didn't order them. The customer might not complain, but our balance sheet will!

"IN" with subqueries, a match made in heaven

The IN clause shows its real power when combined with subqueries. Need to match values that are the result of another SELECT statement? IN got you covered.

SELECT *
FROM suppliers
WHERE supplier_id IN (SELECT supplier_id FROM orders WHERE order_date >= '2023-01-01'); -- New Year Bash suppliers only please!

“IN” vs. "JOIN"

While the IN clause is powerful, JOIN offers similar functionalities in certain scenarios. Joins may provide better efficiency and readability, especially when you need to match values from another table.

SELECT p.*
FROM products p
JOIN orders o ON p.product_id = o.product_id
WHERE o.order_date = '2023-03-15'; -- Only the St. Patrick's Day hangover cures please!

In this example, we get product details for orders made on a particular date, with less SQL wizardry.

Anticipating and mitigating pitfalls

The IN clause is a powerful tool, but it can spring surprises. Look out for:

Subquery performance: A large dataset from an IN subquery might slow things down. Indexing can help.
NULL values: IN statements ignore NULL. Always use IS NULL to explicitly check for NULL.
Data type consistency: Ensure that the IN list values match the data type of the column you're checking.