Home / SQL / IN

IN

Overview

The IN operator allows you to specify multiple values in a WHERE clause. It's a shorthand for multiple OR conditions, making queries more concise and readable.

IN is useful when you need to check if a value matches any value in a list of possibilities.

Basic Syntax

IN Syntax

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

IN vs Multiple OR Conditions

IN is equivalent to multiple OR conditions but more concise:

Using OR (verbose)

SELECT * FROM Customers
WHERE city = 'New York' 
   OR city = 'Los Angeles' 
   OR city = 'Chicago' 
   OR city = 'Houston';

Using IN (concise)

SELECT * FROM Customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago', 'Houston');

Both queries return the same results, but IN is cleaner and easier to maintain.

Examples

Example 1: Simple IN with Strings

Multiple cities

SELECT name, city, email
FROM Customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago');

Example 2: IN with Numeric Values

Multiple IDs

SELECT *
FROM Products
WHERE product_id IN (1, 2, 3, 5, 8, 13);

Example 3: IN with Status Values

Multiple statuses

SELECT *
FROM Orders
WHERE status IN ('Pending', 'Processing', 'Shipped');

IN with NOT

Use NOT IN to exclude values from a list:

Exclude specific values

SELECT *
FROM Customers
WHERE city NOT IN ('New York', 'Los Angeles');

IN with Subqueries

IN can be used with subqueries to check if a value exists in the result of another query:

IN with subquery

-- Find customers who have placed orders
SELECT *
FROM Customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id 
    FROM Orders
);

Example: Customers with orders above threshold

Complex subquery example

SELECT *
FROM Customers
WHERE customer_id IN (
    SELECT customer_id 
    FROM Orders 
    WHERE total_amount > 1000
);

IN vs EXISTS

IN and EXISTS can sometimes achieve similar results, but they work differently:

IN vs EXISTS

-- Using IN
SELECT * FROM Customers
WHERE customer_id IN (
    SELECT customer_id FROM Orders
);

-- Using EXISTS (often more efficient)
SELECT * FROM Customers c
WHERE EXISTS (
    SELECT 1 FROM Orders o 
    WHERE o.customer_id = c.customer_id
);

Note: EXISTS can be more efficient for large datasets as it stops searching once a match is found.

IN with NULL Values

Be careful with NULL values when using IN:

NULL handling

-- IN with NULL doesn't work as expected
SELECT * FROM Customers
WHERE city IN ('New York', 'Los Angeles', NULL);
-- NULL in the list doesn't match NULL in the database

-- Use IS NULL separately
SELECT * FROM Customers
WHERE city IN ('New York', 'Los Angeles') 
   OR city IS NULL;

Performance Considerations

Small lists: IN is efficient for small lists of values
Large lists: Consider using JOINs or EXISTS for large lists
Indexed columns: IN works well with indexed columns
Subqueries: IN with subqueries may be slower than JOINs

Common Use Cases

Filtering by multiple categories
Selecting records from a list of IDs
Filtering by multiple status values
Finding records matching any value in a set
Combining multiple OR conditions more cleanly

Best Practices

Use IN for small lists: More readable than multiple ORs
Use JOINs for large sets: More efficient than IN with large subqueries
Avoid NULL in lists: Handle NULLs separately
Order values: Keep lists organized for maintainability
Consider EXISTS: For subqueries, EXISTS may be more efficient

Next Steps

Learn about range matching with BETWEEN, or explore pattern matching with LIKE.

← Wildcards Between →