PostgreSQL IN

IN Operator Patterns

WHERE status IN ('Active', 'Pending')

WHERE id IN (1, 5, 10, 15)

WHERE country NOT IN ('USA', 'Canada')

WHERE id IN (SELECT user_id FROM orders)

🔹 Basic IN Operator

The IN operator simplifies queries by replacing multiple OR conditions with a single list. Instead of writing multiple OR statements, you list all values in parentheses. This makes code more readable and maintainable, especially when checking against many values. Works with any data type including text, numbers, and dates.

-- Sample data: employees table
-- emp_id | name          | department
-- 1      | John Smith    | Sales
-- 2      | Jane Doe      | IT
-- 3      | Mike Johnson  | HR
-- 4      | Sarah Wilson  | Sales

-- Find employees in Sales or IT departments
SELECT name, department FROM employees
WHERE department IN ('Sales', 'IT');

🔹 IN vs Multiple OR

The IN operator is cleaner and more efficient than writing multiple OR conditions. Both produce the same results, but IN is shorter and easier to understand. When you have many values to check, IN significantly reduces code length and improves readability, making maintenance easier for you and your team.

-- Using OR (longer, harder to read)
SELECT * FROM products
WHERE category = 'Electronics' 
   OR category = 'Computers' 
   OR category = 'Phones';

-- Using IN (cleaner, easier to read)
SELECT * FROM products
WHERE category IN ('Electronics', 'Computers', 'Phones');

🔹 IN with Numeric Values

The IN operator works perfectly with numeric data types including integers, decimals, and floating-point numbers. You don't need quotes around numbers in the list. This is useful for filtering by specific IDs, quantities, prices, or any numeric criteria where you need to match exact values.

-- Find products with specific IDs
SELECT product_name, price FROM products
WHERE product_id IN (101, 105, 110, 115);

🔹 NOT IN Operator

Use NOT IN to exclude specific values from your results. It returns all rows where the column value doesn't match any value in the list. This is useful for filtering out test data, inactive records, or specific categories you want to exclude from analysis or reports.

-- Find customers NOT from these cities
SELECT name, city FROM customers
WHERE city NOT IN ('New York', 'Los Angeles', 'Chicago');

🔹 IN with Subquery

Combine IN with a subquery to match values against results from another query. The subquery runs first and returns a list of values, then IN checks if the column matches any of those values. This is powerful for complex filtering based on related data from other tables.

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

🔹 IN with Multiple Conditions

Combine IN with other WHERE conditions using AND or OR for complex filtering. The IN operator can be part of larger conditional logic, allowing you to create sophisticated queries. This gives you flexibility to filter data based on multiple criteria simultaneously, making your queries more powerful and precise.

-- Find high-value orders from specific regions
SELECT order_id, region, amount FROM orders
WHERE region IN ('North', 'South', 'East')
  AND amount > 1000;