Filtering (WHERE)

SELECT * FROM employees WHERE department = 'Engineering';

-- Multiple conditions
SELECT * FROM orders
WHERE status = 'pending'
  AND total > 100
  AND created_at >= '2024-01-01';

-- AND: both must be true
WHERE age > 18 AND country = 'US'

-- OR: either must be true
WHERE status = 'active' OR status = 'trial'

-- NOT: inverts condition
WHERE NOT deleted
WHERE NOT (status = 'inactive' OR status = 'banned')

-- Precedence: NOT > AND > OR
-- Always use parentheses to make intent explicit
WHERE (country = 'US' OR country = 'CA') AND verified = TRUE

-- Match any value in a list
SELECT * FROM products WHERE category IN ('Electronics', 'Books', 'Toys');

-- NOT IN
SELECT * FROM users WHERE role NOT IN ('admin', 'moderator');

-- IN with subquery
SELECT * FROM orders WHERE user_id IN (
    SELECT id FROM users WHERE plan = 'premium'
);

-- Gotcha: NOT IN with NULLs returns no rows
-- If subquery can return NULL, use NOT EXISTS instead
SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);    -- broken if b has NULLs
SELECT * FROM a WHERE NOT EXISTS (
    SELECT 1 FROM b WHERE b.id = a.id
);                                                      -- safe

-- Inclusive on both ends
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
-- Equivalent to: WHERE price >= 10 AND price <= 100

-- NOT BETWEEN
SELECT * FROM events WHERE event_date NOT BETWEEN '2024-01-01' AND '2024-12-31';

-- Works on strings (lexicographic order)
SELECT * FROM employees WHERE last_name BETWEEN 'A' AND 'M';

-- Works on dates
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- % matches zero or more characters
WHERE email LIKE '%@gmail.com'       -- ends with @gmail.com
WHERE name LIKE 'A%'                 -- starts with A
WHERE description LIKE '%keyword%'   -- contains keyword

-- _ matches exactly one character
WHERE code LIKE 'US-_'              -- US-A, US-B, etc.
WHERE phone LIKE '___-___-____'     -- NNN-NNN-NNNN

-- NOT LIKE
WHERE name NOT LIKE 'test%'

-- ILIKE — case-insensitive (PostgreSQL)
WHERE email ILIKE '%@GMAIL.COM'

-- ESCAPE: treat % or _ literally
WHERE path LIKE '100\%' ESCAPE '\'  -- literal percent sign

-- MySQL: LIKE is case-insensitive for non-binary columns by default
-- SQLite: LIKE is case-insensitive for ASCII letters by default

-- PostgreSQL: ~ (case-sensitive), ~* (case-insensitive)
WHERE email ~ '^[a-z]+@'
WHERE name ~* 'alice'

-- PostgreSQL: !~ (not match), !~* (not match, case-insensitive)
WHERE name !~ '^[0-9]'

-- PostgreSQL: SIMILAR TO (SQL standard regex, limited)
WHERE name SIMILAR TO '[A-Z][a-z]+'

-- MySQL: REGEXP / RLIKE
WHERE email REGEXP '^[a-z]'
WHERE name RLIKE 'alice|bob'

-- SQL Server: no native regex without CLR; use LIKE

-- REGEXP_LIKE (Oracle, MySQL 8+)
WHERE REGEXP_LIKE(email, '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$')

-- Correct
WHERE phone IS NULL
WHERE phone IS NOT NULL

-- Wrong — always evaluates to NULL, never TRUE
WHERE phone = NULL        -- never use this
WHERE phone != NULL       -- never use this

-- IS DISTINCT FROM: NULL-safe not-equal (PostgreSQL, SQL:2003)
WHERE a IS DISTINCT FROM b     -- TRUE even when one side is NULL
WHERE a IS NOT DISTINCT FROM b -- TRUE when both are NULL

-- COALESCE to treat NULL as a value
WHERE COALESCE(discount, 0) > 0

-- Exact date
WHERE order_date = '2024-06-15'

-- Date range (half-open interval avoids time-of-day issues)
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'

-- Relative dates (PostgreSQL)
WHERE created_at >= NOW() - INTERVAL '7 days'
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'

-- Extract date part
WHERE EXTRACT(YEAR FROM created_at) = 2024
WHERE EXTRACT(DOW FROM created_at) = 0    -- 0 = Sunday (PostgreSQL)

-- Date trunc equality
WHERE DATE_TRUNC('month', created_at) = '2024-06-01'

-- MySQL equivalents
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
WHERE YEAR(created_at) = 2024
WHERE MONTH(created_at) = 6

-- EXISTS: true if subquery returns at least one row (short-circuits)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- NOT EXISTS: true if subquery returns no rows
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

-- EXISTS is usually faster than IN for large subqueries
-- because it short-circuits on the first match

-- WRONG: aggregate in WHERE
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 70000    -- ERROR
GROUP BY department;

-- CORRECT: use HAVING
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

-- Both WHERE and HAVING in same query
SELECT department, COUNT(*) AS cnt
FROM employees
WHERE status = 'active'        -- filter rows first
GROUP BY department
HAVING COUNT(*) >= 5;          -- then filter groups

-- Filter on window function result (must wrap in subquery/CTE)
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn = 1;

-- ANY / ALL with subquery
WHERE salary > ANY (SELECT salary FROM employees WHERE dept = 'Intern')
WHERE salary > ALL (SELECT salary FROM employees WHERE dept = 'Manager')
-- ANY = at least one; ALL = every row in subquery

-- FILTER clause on aggregates (PostgreSQL)
SELECT
    COUNT(*) FILTER (WHERE status = 'active')   AS active_count,
    COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;

-- Bitmap/array containment (PostgreSQL)
WHERE tags @> ARRAY['sql','database']    -- array contains all elements
WHERE 'premium' = ANY(roles)             -- element in array

-- JSONB filtering (PostgreSQL)
WHERE profile->>'plan' = 'premium'
WHERE profile @> '{"verified": true}'

-- Full-text search (PostgreSQL)
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'sql & tutorial')

-- Full-text search (MySQL)
WHERE MATCH(title, body) AGAINST ('sql tutorial' IN NATURAL LANGUAGE MODE)

-- Case-insensitive equality without ILIKE
WHERE LOWER(email) = LOWER('User@Example.com')
WHERE UPPER(code) = 'ABC123'

-- Starts with (can use index if LIKE 'prefix%')
WHERE username LIKE 'john%'

-- Contains (cannot use standard index)
WHERE description LIKE '%keyword%'
-- Use full-text index or trigram index (PostgreSQL pg_trgm) for contains queries