SQL AND

-- All three must be true
SELECT id, email
FROM users
WHERE country = 'AU'
  AND active = TRUE
  AND created_at > '2025-01-01';

-- Parentheses control precedence
SELECT id
FROM orders
WHERE (status = 'paid' OR status = 'shipped')
  AND total >= 100;

-- WITHOUT the parens this would mean:
-- status = 'paid' OR (status = 'shipped' AND total >= 100)
-- which is NOT what you wanted.

-- Combine with BETWEEN for ranges
SELECT id
FROM orders
WHERE total BETWEEN 100 AND 500
  AND created_at >= '2026-01-01';

-- Combine with IN
SELECT id
FROM users
WHERE country IN ('AU', 'NZ', 'US')
  AND tier = 'pro';

-- Combine with LIKE
SELECT id
FROM users
WHERE email LIKE '%@example.com'
  AND active = TRUE;

-- NULL is tricky - = does not match NULL
SELECT id
FROM users
WHERE active = TRUE
  AND deleted_at IS NULL;

-- Index hint
-- If country + active is a common combination, a compound index
-- CREATE INDEX users_country_active ON users (country, active);
-- helps both 'WHERE country=' and 'WHERE country AND active'.

-- Equivalent to JOIN ON conditions
SELECT u.id
FROM users u
JOIN orders o ON o.user_id = u.id
            AND o.status = 'paid';
-- JOIN ON is just AND between the joined rows.

-- Common mistake: forgetting AND between WHERE and JOIN ON
-- WHERE conditions filter the final result; JOIN ON filters per row.

SELECT * FROM products
WHERE price > 50 AND stock > 0;