SQL OR

-- Either condition matches
SELECT id, status
FROM orders
WHERE status = 'paid' OR status = 'shipped';

-- Equivalent (and usually clearer) with IN
SELECT id, status
FROM orders
WHERE status IN ('paid', 'shipped');

-- AND has higher precedence than OR; parenthesise to be explicit
SELECT id
FROM orders
WHERE (status = 'paid' OR status = 'shipped')
  AND total >= 100;

-- Without parens:
-- status = 'paid' OR (status = 'shipped' AND total >= 100)
-- The 'paid' branch ignores the total filter. Common bug.

-- OR can hurt indexes
-- If you have an index on status alone, this is fine:
SELECT id FROM orders WHERE status = 'paid' OR status = 'shipped';

-- This often forces a full scan even with indexes on both columns:
SELECT id FROM orders WHERE status = 'paid' OR total > 1000;

-- Rewrite with UNION ALL to keep both indexes
SELECT id FROM orders WHERE status = 'paid'
UNION ALL
SELECT id FROM orders WHERE total > 1000 AND status != 'paid';

-- Multi-column conditions
SELECT *
FROM users
WHERE (country = 'AU' AND tier = 'pro')
   OR (country = 'NZ' AND tier IN ('pro', 'premium'));

-- NULL gotcha
SELECT id FROM users WHERE name = 'Ada' OR name IS NULL;
-- 'name = NULL' would NOT match nulls - use IS NULL explicitly

-- CASE inside SELECT for OR-like branching
SELECT
  id,
  CASE
    WHEN status = 'paid'    THEN 'green'
    WHEN status = 'pending' THEN 'amber'
    ELSE 'red'
  END AS colour
FROM orders;

SELECT * FROM customers
WHERE country = 'AU' OR country = 'NZ';