What Are IN and BETWEEN?

-- IN: match against a list
SELECT * FROM employees WHERE department IN ('Engineering', 'Sales');

-- BETWEEN: match within a range
SELECT * FROM employees WHERE salary BETWEEN 60000 AND 80000;

IN Operator

IN replaces multiple OR conditions on the same column with a cleaner syntax.

-- IN with numeric values
SELECT * FROM products
WHERE category_id IN (1, 2, 3, 4, 5);

-- IN with string values
SELECT * FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');

-- IN with date values
SELECT * FROM orders
WHERE order_date IN ('2024-01-01', '2024-06-15', '2024-12-31');

-- IN is equivalent to multiple OR conditions
-- These two queries return the same result:
SELECT * FROM employees WHERE department IN ('Engineering', 'Sales', 'Marketing');
SELECT * FROM employees
WHERE department = 'Engineering'
OR department = 'Sales'
OR department = 'Marketing';

IN with Subqueries

IN becomes powerful when the list is generated dynamically from another table.

-- Find customers who have placed orders
SELECT first_name, last_name
FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);

-- Find products in active categories
SELECT * FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE status = 'Active'
);

-- Find employees in high-budget departments
SELECT * FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE budget > 1000000
);

NOT IN

NOT IN returns rows where the column value does not match any value in the list.

-- Find employees NOT in specific departments
SELECT * FROM employees
WHERE department NOT IN ('Engineering', 'Sales');

-- Find customers who have never placed orders
SELECT * FROM customers
WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders);

-- Safer alternative: NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

-- Also safe: LEFT JOIN
SELECT c.* FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

BETWEEN Operator

BETWEEN checks whether a value falls within an inclusive range. It works with numbers, strings, and dates.

-- Numeric range
SELECT * FROM products
WHERE price BETWEEN 50 AND 100;

-- String range (alphabetical ordering)
SELECT * FROM customers
WHERE last_name BETWEEN 'A' AND 'M';

-- Date range
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- NOT BETWEEN
SELECT * FROM employees
WHERE salary NOT BETWEEN 50000 AND 80000;

BETWEEN with DateTime

DateTime ranges require careful handling because BETWEEN is inclusive on both ends.

-- Incorrect: misses orders on Jan 31 after midnight
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

-- Correct: includes all of Jan 31
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';

-- Current year orders using functions
SELECT * FROM orders
WHERE order_date BETWEEN
    DATE_FORMAT(CURRENT_DATE, '%Y-01-01')
    AND DATE_FORMAT(CURRENT_DATE, '%Y-12-31');

Performance Considerations

-- IN can use indexes
SELECT * FROM employees
WHERE department IN ('Engineering', 'Sales', 'Marketing');

-- BETWEEN uses range scan
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- For very large IN lists, consider a JOIN
SELECT e.* FROM employees e
INNER JOIN (
    SELECT 'Engineering' AS dept
    UNION ALL SELECT 'Sales'
    UNION ALL SELECT 'Marketing'
) d ON e.department = d.dept;

Practice Exercises

Exercise 1: Find all Electronics or Clothing products priced between 100.

-- Solution
SELECT * FROM products
WHERE category IN ('Electronics', 'Clothing')
AND price BETWEEN 20 AND 100;

Exercise 2: Find customers from major cities who registered in 2024.

-- Solution
SELECT * FROM customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago')
AND registration_date BETWEEN '2024-01-01' AND '2024-12-31';

Exercise 3: Find orders that are NOT in pending or cancelled status.

-- Solution
SELECT * FROM orders
WHERE status NOT IN ('Pending', 'Cancelled');

Summary