SQL WHERE IN List: Complete Guide

Understanding WHERE IN

The SQL WHERE IN clause lets you check if a value matches any item in a list of values. It’s particularly useful when you need to filter rows based on multiple possible matches, making your queries cleaner and more efficient than multiple OR conditions.

Let’s start with a basic example:

SELECT * FROM products
WHERE category IN ('Electronics', 'Books', 'Games');

This query:
- Searches the products table
- Returns rows where category matches any of the three values
- Is equivalent to: WHERE category = ‘Electronics’ OR category = ‘Books’ OR category = ‘Games’

Using WHERE IN with Numbers

Numbers work just as well with IN clauses:

SELECT 
    order_id,
    customer_name,
    order_total
FROM orders
WHERE order_status_id IN (1, 3, 5)
    AND order_total > 100;

This query:
- Finds orders with specific status IDs (perhaps 1 for ‘pending’, 3 for ‘shipped’, 5 for ‘delivered’)
- Adds an additional filter for order_total
- Shows how IN clauses can be combined with other WHERE conditions

Dynamic Lists with Subqueries

Instead of hard-coding values, you can use subqueries to create dynamic lists:

SELECT 
    product_id,
    product_name,
    unit_price
FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE active = 1
    AND last_updated >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
);

This example:
- Finds products in categories that are currently active
- Only includes categories updated in the last 30 days
- Demonstrates how IN can use results from another query

Multiple Column IN Clauses

You can check multiple columns at once using IN:

SELECT 
    employee_id,
    department,
    hire_date
FROM employees
WHERE (department, location_id) IN (
    SELECT department, location_id
    FROM department_locations
    WHERE is_remote_allowed = 1
    AND available_positions > 0
);

This query:
- Matches two columns simultaneously
- Finds employees in departments that allow remote work
- Shows departments with open positions

NOT IN for Exclusion

Sometimes you need to find rows that don’t match a list:

SELECT 
    customer_id,
    email,
    last_order_date
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
)
AND is_active = 1;

This query:
- Identifies active customers who haven’t ordered in 90 days
- Could be useful for a re-engagement campaign
- Demonstrates NOT IN usage with a subquery

Working with NULL Values

Be careful with NULL values when using IN and NOT IN:

SELECT 
    product_id,
    product_name,
    last_ordered_date
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
    WHERE product_id IS NOT NULL
)
AND status = 'Active';

This query:
- Finds active products that have never been ordered
- Explicitly handles NULL values to avoid unexpected results
- Shows a common pattern for dealing with NULLs in NOT IN clauses

Using IN with Multiple Tables

Here’s how to use IN across multiple related tables:

SELECT 
    p.product_id,
    p.product_name,
    c.category_name,
    s.supplier_name
FROM products p
JOIN categories c ON p.category_id = c.category_id
JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE p.category_id IN (
    SELECT category_id
    FROM category_sales
    WHERE annual_revenue > 1000000
)
AND p.supplier_id IN (
    SELECT supplier_id
    FROM supplier_ratings
    WHERE quality_score >= 4.5
);

This query:
- Finds products in high-revenue categories
- From highly-rated suppliers
- Shows how to combine multiple IN clauses with JOINs

Performance Considerations

When dealing with large lists, consider using temporary tables:

CREATE TEMPORARY TABLE high_value_products AS
SELECT product_id
FROM order_items
GROUP BY product_id
HAVING SUM(quantity * unit_price) > 10000;

SELECT 
    p.product_id,
    p.product_name,
    p.stock_level
FROM products p
WHERE p.product_id IN (
    SELECT product_id
    FROM high_value_products
)
AND p.stock_level < 100;

DROP TEMPORARY TABLE IF EXISTS high_value_products;

This approach:
- Creates a temporary table for the IN list
- Can improve performance for repeated queries
- Cleans up the temporary table when done

Real-World Application: Email Campaign

Here’s a practical example for creating an email campaign list:

SELECT 
    c.customer_id,
    c.email,
    c.first_name,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IN (
    -- Customers who bought specific products
    SELECT DISTINCT customer_id
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE oi.product_id IN (101, 102, 103)
    AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 180 DAY)
)
AND c.email_subscription = 1
AND c.customer_id NOT IN (
    -- Exclude customers with recent complaints
    SELECT customer_id
    FROM support_tickets
    WHERE created_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    AND ticket_type = 'Complaint'
)
GROUP BY 
    c.customer_id,
    c.email,
    c.first_name
HAVING last_order_date < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

This query:
- Finds customers who bought specific products
- Excludes those with recent complaints
- Ensures they haven’t ordered in the last 30 days
- Checks for email subscription consent
- Could be used for a targeted promotion

List Validation Techniques

When working with user-provided lists, validation is important:

WITH valid_products AS (
    SELECT product_id
    FROM products
    WHERE product_id IN (1, 2, 3, 4, 5)
    AND discontinued = 0
),
invalid_products AS (
    SELECT 
        unnested.product_id
    FROM (
        SELECT 1 as product_id
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
    ) as unnested
    WHERE unnested.product_id NOT IN (
        SELECT product_id
        FROM valid_products
    )
)
SELECT * FROM invalid_products;

This query:
- Validates a list of product IDs
- Identifies which IDs are invalid
- Uses a Common Table Expression (CTE) for clarity

Get ryan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Understanding these patterns and implementing them appropriately will help you write more effective SQL queries. Remember to consider performance implications when working with large datasets and always test your queries with representative data volumes.

Handling Large IN Lists Efficiently

When working with hundreds or thousands of values, putting them directly in the IN clause can cause problems. Here’s a better approach:

-- Step 1: Create a temporary table with an index
CREATE TEMPORARY TABLE filtered_ids (
    id INT PRIMARY KEY
);

-- Step 2: Insert the values we want to check against
INSERT INTO filtered_ids (id) VALUES 
    (1), (2), (3), (4), (5);

-- Step 3: Create an index for performance
CREATE INDEX idx_filtered_ids ON filtered_ids(id);

-- Step 4: Use the temporary table in our query
SELECT 
    p.product_id,
    p.product_name,
    p.category,
    p.stock_level
FROM products p
WHERE p.product_id IN (
    SELECT id 
    FROM filtered_ids
);

-- Step 5: Clean up
DROP TEMPORARY TABLE IF EXISTS filtered_ids;

Let’s break down why this approach is better:
1. The temporary table prevents query text size limits
2. The PRIMARY KEY constraint ensures unique values
3. The index makes lookups much faster
4. The table can be reused for multiple queries
5. It’s easier to update or modify the list

Working with Dynamic String Lists

Often you’ll receive a comma-separated string of values. Here’s how to handle that:

WITH RECURSIVE number_sequence AS (
    -- Generate numbers from 1 to 100
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 
    FROM number_sequence 
    WHERE n < 100
),
split_values AS (
    -- Split the string into individual values
    SELECT 
        n,
        TRIM(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX('1,2,3,4,5', ',', n),
                ',',
                -1
            )
        ) AS value
    FROM number_sequence
    WHERE n <= (
        LENGTH('1,2,3,4,5') 
        - LENGTH(REPLACE('1,2,3,4,5', ',', '')) 
        + 1
    )
)
SELECT *
FROM products
WHERE product_id IN (
    SELECT CAST(value AS UNSIGNED)
    FROM split_values
    WHERE value REGEXP '^[0-9]+$'
);

Breaking down each part:
1. The `number_sequence` CTE:
— Creates a sequence of numbers (1,2,3…)
— Used to split the string

2. The `split_values` CTE:
— `SUBSTRING_INDEX(str, ‘,’, n)` gets first n values
— Outer `SUBSTRING_INDEX` gets the last value
— `TRIM()` removes any whitespace

3. The main query:
— `CAST(value AS UNSIGNED)` converts to numbers
— `REGEXP ‘^[0–9]+$’` ensures valid numbers only

Advanced Filtering with Multiple IN Conditions

Here’s how to combine multiple IN clauses effectively:

WITH active_categories AS (
    SELECT category_id
    FROM categories
    WHERE status = 'Active'
    AND last_updated >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
),
preferred_suppliers AS (
    SELECT supplier_id
    FROM suppliers
    WHERE rating >= 4.0
    AND active = 1
)
SELECT 
    p.product_id,
    p.product_name,
    c.category_name,
    s.supplier_name,
    p.unit_price,
    p.stock_level
FROM products p
JOIN categories c ON p.category_id = c.category_id
JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE p.category_id IN (
    SELECT category_id FROM active_categories
)
AND p.supplier_id IN (
    SELECT supplier_id FROM preferred_suppliers
)
AND p.status IN ('In Stock', 'Low Stock')
AND p.price_tier IN (
    SELECT tier_id 
    FROM price_tiers 
    WHERE is_promotional = 1
);

Let’s examine what each part does:
1. The `active_categories` CTE:
— Finds recently updated categories
— Caches results for multiple uses

2. The `preferred_suppliers` CTE:
— Identifies high-rated suppliers
— Creates a filtered supplier list

3. The main query joins:
— Products to categories
— Products to suppliers
— Uses aliases for readability

4. The WHERE clause combines:
— Category filter from first CTE
— Supplier filter from second CTE
— Product status check
— Price tier check

Handling NULL Values Correctly

NULL values require special attention with IN clauses:

SELECT 
    product_id,
    product_name,
    stock_level,
    last_ordered_date
FROM products p
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_items
    WHERE product_id IS NOT NULL
    AND order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
)
AND status = 'Active'
AND product_id IS NOT NULL;

Why this handling is important:
1. `WHERE product_id IS NOT NULL` in subquery:
— Prevents NULL from entering the IN list
— NULL in IN list makes entire condition NULL

2. `AND product_id IS NOT NULL` in main query:
— Double-checks main table values
— Ensures reliable results

3. Common pitfalls avoided:
— NOT IN with NULL values returns no rows
— Missing NULL checks can hide data
— Incorrect results can appear valid

Performance Optimization with IN Lists

Here’s how to structure IN clauses for better performance:

-- First, create an indexed temp table
CREATE TEMPORARY TABLE high_value_orders (
    order_id INT PRIMARY KEY
);

-- Populate with filtered data
INSERT INTO high_value_orders
SELECT order_id
FROM orders
WHERE total_amount > 1000
AND order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

-- Create supporting index
CREATE INDEX idx_order_id ON high_value_orders(order_id);

-- Use in multiple queries
SELECT 
    oi.order_id,
    oi.product_id,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) as line_total
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE oi.order_id IN (
    SELECT order_id 
    FROM high_value_orders
)
ORDER BY oi.order_id, oi.product_id;

Performance benefits explained:
1. Temporary table advantages:
— Indexed lookups are faster than IN lists
— Results cached for multiple uses
— Reduces repeated subquery execution

2. Index usage:
— Supports efficient data retrieval
— Helps optimize join operations
— Reduces full table scans

3. Query optimization:
— Filtered data ready for use
— Reduces repeated calculations
— Improves overall performance

Remember these key points when using IN clauses:
- Always consider data volume
- Index commonly filtered columns
- Use temporary tables for large lists
- Handle NULL values explicitly
- Test performance with realistic data

These techniques help you write more efficient and maintainable queries while avoiding common pitfalls with IN clauses.