MySQL IN

Key IN Concepts

WHERE status IN 
('active', 'pending')

WHERE status NOT IN 
('deleted', 'banned')

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

WHERE id IN 
(SELECT customer_id 
FROM orders)

🔹 Basic IN Syntax

The IN operator simplifies queries that would otherwise require multiple OR conditions. It checks if a column value matches any value in a specified list. This makes your SQL more concise, readable, and easier to maintain than chaining OR statements.

-- Basic syntax
SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2, value3);

-- Example with cities
SELECT name, city, country
FROM customers
WHERE city IN ('Paris', 'London', 'Berlin');

-- Example with numbers
SELECT product_name, category_id
FROM products
WHERE category_id IN (1, 3, 5, 7);

🔹 IN vs Multiple OR

The IN operator is a shorthand for multiple OR conditions. Both produce the same results, but IN is cleaner, more readable, and less prone to errors. It's especially beneficial when you have many values to check against, reducing code complexity significantly.

-- Using multiple OR (verbose)
SELECT * FROM products
WHERE category = 'Electronics'
   OR category = 'Computers'
   OR category = 'Phones'
   OR category = 'Tablets';

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

-- Both queries return the same results
-- IN is more readable and maintainable

🔹 IN with Text Values

Use IN with string values to filter by multiple text options. Enclose each text value in quotes and separate them with commas. This is perfect for filtering by status, category, name, or any text-based criteria where you need to match several specific values.

-- Filter by multiple statuses
SELECT order_id, customer_name, status
FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

-- Filter by employee departments
SELECT name, department, salary
FROM employees
WHERE department IN ('Sales', 'Marketing', 'IT');

-- Filter by product names
SELECT * FROM products
WHERE product_name IN ('Laptop', 'Mouse', 'Keyboard', 'Monitor');

-- Filter by country codes
SELECT * FROM customers
WHERE country_code IN ('US', 'CA', 'UK', 'AU');

🔹 IN with Numeric Values

IN works seamlessly with numbers, IDs, and numeric codes. No quotes needed for numeric values. This is commonly used for filtering by specific IDs, category numbers, price points, or any numeric identifiers when you need to match multiple specific numbers.

-- Filter by specific IDs
SELECT * FROM customers
WHERE customer_id IN (1, 5, 10, 15, 20);

-- Filter by category IDs
SELECT product_name, price
FROM products
WHERE category_id IN (2, 4, 6);

-- Filter by specific years
SELECT * FROM orders
WHERE YEAR(order_date) IN (2022, 2023, 2024);

-- Filter by price points
SELECT * FROM products
WHERE price IN (9.99, 19.99, 29.99, 49.99);

🔹 NOT IN Operator

NOT IN excludes rows where the column value matches any value in the list. It's the opposite of IN, useful for filtering out unwanted data like inactive users, deleted records, or specific categories you want to exclude from your results.

-- Exclude specific statuses
SELECT * FROM orders
WHERE status NOT IN ('cancelled', 'refunded', 'deleted');

-- Exclude specific departments
SELECT name, department, salary
FROM employees
WHERE department NOT IN ('Intern', 'Contractor');

-- Exclude specific IDs
SELECT * FROM products
WHERE product_id NOT IN (5, 10, 15);

-- Exclude test accounts
SELECT * FROM users
WHERE username NOT IN ('test', 'admin', 'demo');

🔹 IN with Subquery

Use a subquery inside IN to dynamically generate the list of values from another table. This is powerful for finding related records, such as customers who have placed orders, products that have been sold, or users who have performed specific actions.

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

-- Find products that have been sold
SELECT product_name, price
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM order_items
);

-- Find employees in departments with > 10 people
SELECT name, department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING COUNT(*) > 10
);

-- Find users who commented today
SELECT username, email
FROM users
WHERE user_id IN (
    SELECT user_id
    FROM comments
    WHERE DATE(created_at) = CURDATE()
);

🔹 Combining IN with Other Conditions

Combine IN with other WHERE conditions using AND and OR for complex filtering. This allows you to create sophisticated queries that filter by multiple criteria simultaneously, giving you precise control over which records are returned from your database.

-- IN with AND condition
SELECT * FROM products
WHERE category IN ('Electronics', 'Computers')
  AND price < 1000
  AND stock_quantity > 0;

-- IN with OR condition
SELECT * FROM customers
WHERE city IN ('New York', 'Los Angeles')
   OR country IN ('Canada', 'Mexico');

-- Multiple IN conditions
SELECT * FROM orders
WHERE status IN ('pending', 'processing')
  AND customer_id IN (1, 5, 10, 15)
  AND YEAR(order_date) IN (2023, 2024);

-- IN with LIKE
SELECT * FROM products
WHERE category IN ('Electronics', 'Computers')
  AND product_name LIKE '%Pro%';

🔹 Practical Examples

Real-world applications of the IN operator in common database scenarios:

-- E-commerce: Active orders in specific statuses
SELECT 
    order_id,
    customer_name,
    total_amount,
    status
FROM orders
WHERE status IN ('pending', 'processing', 'shipped')
  AND order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY order_date DESC;

-- HR: Employees in specific roles
SELECT 
    employee_id,
    name,
    position,
    salary
FROM employees
WHERE position IN ('Manager', 'Senior Developer', 'Team Lead')
  AND department IN ('IT', 'Engineering')
ORDER BY salary DESC;

-- Content: Published posts in categories
SELECT 
    title,
    author,
    category,
    published_date
FROM blog_posts
WHERE category IN ('Technology', 'Programming', 'Database')
  AND status = 'published'
  AND published_date >= '2024-01-01'
ORDER BY published_date DESC;

-- Analytics: Users from specific countries
SELECT 
    user_id,
    username,
    country,
    signup_date
FROM users
WHERE country IN ('US', 'UK', 'CA', 'AU')
  AND account_type IN ('premium', 'enterprise')
  AND is_active = 1;