SQL Predicates — Applied IN Subqueries and ANY/ALL

SELECT
  p.product_id, p.name, p.category, p.price
FROM   products p
WHERE  NOT EXISTS (
  SELECT 1
  FROM   order_items oi
  WHERE  oi.product_id = p.product_id  -- Correlation condition: FALSE if even one match exists
)
ORDER BY p.product_id;

-- ✗ Incorrect form (returns zero rows when order_items.product_id contains NULL):
-- WHERE p.product_id NOT IN (SELECT product_id FROM order_items)

/*
  Execution order (SQL's logical evaluation order):
  1. FROM products p                     → outer: scan 6 rows
  2. [For each row] correlated NOT EXISTS subquery:
       FROM order_items oi
       WHERE oi.product_id = p.product_id → one or more matches → FALSE (excluded)
                                            zero matches             → TRUE (passes)
  3. WHERE NOT EXISTS (...)              → keep 2 TRUE rows (products 5 and 6)
  4. SELECT p.product_id, p.name, ...   → select 4 columns
  5. ORDER BY p.product_id              → sort by ID ascending
*/

SELECT
  o.order_id, o.user_id, o.amount, o.status, o.ordered_at
FROM   orders o
WHERE  o.user_id IN (
  SELECT user_id          -- Dynamically retrieve premium members' user_id values
  FROM   users
  WHERE  plan = 'premium'  -- Subquery result: {1, 3}
)
ORDER BY o.ordered_at;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the non-correlated subquery → retrieve premium users once
  2. FROM orders o                      → read 7 rows
  3. WHERE o.user_id IN (...)           → filter to orders from matching users
  4. SELECT o.order_id, o.user_id, ...  → select 5 columns
  5. ORDER BY o.ordered_at              → sort by date ascending
  */

SELECT
  product_id, name, category, price
FROM   products
WHERE  price > ALL (
  SELECT price                       -- Price list for all Accessories products: {12800, 980}
  FROM   products
  WHERE  category = 'Accessories'
)
ORDER BY price;                      -- ↑ Equivalent to price > MAX(Accessories prices) = price > 12800

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the subquery                  → generate the Accessories price set
  2. FROM products                         → read 6 rows
  3. WHERE price > ALL (...)               → filter above the maximum
  4. SELECT product_id, name, ...  → select 4 columns
  5. ORDER BY price                        → sort by price ascending
  */

SELECT
  order_id, user_id, amount, coupon_code, ordered_at
FROM   orders
WHERE  coupon_code IS DISTINCT FROM 'SUMMER10'
--              ↑ NULL-safe comparison: NULL is treated as different from SUMMER10 = TRUE
--   ✗ coupon_code <> 'SUMMER10' makes NULL rows UNKNOWN → they are excluded
ORDER BY order_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM orders                                    → read 7 rows
  2. WHERE coupon_code IS DISTINCT FROM 'SUMMER10'  → keep non-matching rows
  3. SELECT order_id, user_id, amount, ...          → select 5 columns
  4. ORDER BY order_id                              → sort by order_id ascending

  ▸ IS DISTINCT FROM truth table (recap):
      a = b             → FALSE  (equal)
      a ≠ b             → TRUE   (different values)
      NULL vs non-NULL  → TRUE   (NULL is “different”)
      NULL vs NULL      → FALSE  (both NULL are “equal”)
*/