SQL In

The IN operator is a shorthand filter used in a WHERE clause to determine if a specific value matches any value within a defined list.

Using IN allows you to replace multiple OR conditions, making your SQL code much shorter, cleaner, and easier to maintain.

Basic Syntax

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

How it Simplifies Code:

Instead of writing a long, repetitive string of OR clauses like this:

-- The messy way
SELECT * FROM customers
WHERE country = 'USA' OR country = 'UK' OR country = 'India' OR country = 'Japan';

You can consolidate the entire filter into a single line:

-- The clean way using
INSELECT * FROM customers
WHERE country IN ('USA', 'UK', 'India', 'Japan');

Reversing the Logic (NOT IN)

By adding the NOT keyword before IN, you tell the database engine to return only the rows whose values are not present in the specified list.

-- Finds all products that are NOT in these three specific categories
SELECT product_name, category FROM products
WHERE category NOT IN ('Electronics', 'Toys', 'Books');

⚠️ Warning with NULL: If your NOT IN list contains a NULL value (e.g., NOT IN ('Sales', NULL)), the entire query will return zero rows. If you need to handle missing data, ensure your subquery or list filters out NULL items first.

Using IN with a Subquery (Dynamic Lists)

Instead of hardcoding a fixed list of values, you can place an internal SELECT statement inside the IN parentheses. This dynamically builds the list based on real-time data from another table.

-- Finds customers who have actually placed an order
SELECT customer_name, email
FROM customersWHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

Key Takeaways

• Data Types: The values inside the parentheses must match the data type of the column you are filtering (e.g., text strings must be wrapped in single quotes, numbers should not be).
• Readability: Using IN makes complex filtering logical and readable for peers reviewing your scripts.