Joins, properly

-- intent: every customer, with their 2026 orders if any
-- BROKEN: silently becomes an INNER JOIN
SELECT c.name, o.total
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
 WHERE o.year = 2026;          -- customers with no orders: o.year is NULL
-- → UNKNOWN → row discarded
-- CORRECT: the condition belongs to the join
SELECT c.name, o.total
  FROM customers c
  LEFT JOIN orders o
    ON o.customer_id = c.id
   AND o.year = 2026;          -- filters the join, preserves the left side
-- the exception that IS correct: an anti-join (ch 17)
SELECT c.name
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
 WHERE o.id IS NULL;        -- deliberately keeps only non-matches

-- MySQL has no FULL OUTER JOIN. the portable emulation:
SELECT a.*, b.* FROM a LEFT JOIN b ON a.k = b.k
UNION
SELECT a.*, b.* FROM a RIGHT JOIN b ON a.k = b.k;

-- UNION (not UNION ALL) is required here — the inner-matching
-- rows appear in both halves and must be deduplicated.

CREATE TABLE orders (id INT PRIMARY KEY, total DECIMAL(10,2));
CREATE TABLE items  (id INT PRIMARY KEY, order_id INT, sku VARCHAR(20));

INSERT INTO orders VALUES (1, 100.00), (2, 50.00);
INSERT INTO items VALUES
  (1,1,'A'), (2,1,'B'), (3,1,'C'),   -- order 1 has THREE items
  (4,2,'D');                             -- order 2 has one

-- the truth
SELECT SUM(total) FROM orders;                 -- 150.00

-- the bug: total is counted once PER ITEM
SELECT SUM(o.total)
  FROM orders o JOIN items i ON i.order_id = o.id;   -- 350.00 !

-- ── three correct fixes ──

-- 1. aggregate BEFORE joining (usually best)
SELECT SUM(o.total), SUM(i.n)
  FROM orders o
  LEFT JOIN (SELECT order_id, COUNT(*) AS n
              FROM items GROUP BY order_id) i
    ON i.order_id = o.id;                         -- 150.00

-- 2. a correlated scalar subquery
SELECT o.id, o.total,
       (SELECT COUNT(*) FROM items i WHERE i.order_id = o.id) AS n
  FROM orders o;

-- 3. DISTINCT the aggregate (works, but often masks the issue)
SELECT SUM(DISTINCT o.total) ...              -- WRONG if two orders
--                                            share a total!

-- employees and their managers
SELECT e.name AS employee, m.name AS manager
  FROM employees e
  LEFT JOIN employees m ON m.id = e.manager_id;
--   LEFT, so the CEO (manager_id IS NULL) is not dropped
-- consecutive rows: pair each with the next
SELECT a.reading, b.reading AS next_reading
  FROM samples a
  JOIN samples b ON b.seq = a.seq + 1;
--   a window function (LAG/LEAD, ch 34) is better here:
--   it needs one pass instead of a join.

-- semi-join: customers who have ordered (each listed once)
SELECT c.* FROM customers c
 WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- anti-join: customers who never have
SELECT c.* FROM customers c
 WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- the LEFT JOIN / IS NULL idiom does the same thing
SELECT c.* FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
 WHERE o.id IS NULL;

CREATE TABLE c (id INT);
CREATE TABLE o (customer_id INT);   -- nullable!

INSERT INTO c VALUES (1),(2),(3);
INSERT INTO o VALUES (1);              -- only customer 1 ordered

-- works: expect customers 2 and 3
SELECT * FROM c WHERE id NOT IN (SELECT customer_id FROM o);
--   → 2, 3   ✓

INSERT INTO o VALUES (NULL);           -- one orphan row appears

-- SAME query. now returns NOTHING.
SELECT * FROM c WHERE id NOT IN (SELECT customer_id FROM o);
--   → (empty)   ✗   no error. no warning.

-- why: for customer 2 the expansion is
--   2 <> 1  AND  2 <> NULL
--   TRUE    AND  UNKNOWN   →  UNKNOWN  →  row dropped

-- ── the fixes ──

-- 1. NOT EXISTS — immune, and the recommended default
SELECT * FROM c
 WHERE NOT EXISTS (SELECT 1 FROM o WHERE o.customer_id = c.id);

-- 2. exclude NULLs explicitly
SELECT * FROM c
 WHERE id NOT IN (SELECT customer_id FROM o
                  WHERE customer_id IS NOT NULL);

-- 3. best of all: make the column NOT NULL so it cannot happen

-- the three most recent orders for each customer
-- Postgres / MySQL 8.0.14+
SELECT c.name, o.id, o.created_at
  FROM customers c,
  LATERAL (
    SELECT id, created_at FROM orders
     WHERE customer_id = c.id          -- ← references the LEFT row
ORDER BY created_at DESC
LIMIT 3
  ) o;

-- LEFT JOIN LATERAL keeps customers with no orders
SELECT c.name, o.id
  FROM customers c
  LEFT JOIN LATERAL (...) o ON TRUE;   -- ON TRUE is required
-- SQL Server spells it CROSS APPLY / OUTER APPLY
SELECT c.name, o.id FROM customers c CROSS APPLY (...) o;

-- NATURAL JOIN: joins on ALL columns with matching names.
SELECT * FROM orders NATURAL JOIN customers;

-- USING: joins on the named columns, which must exist in both.
SELECT * FROM orders JOIN customers USING (customer_id);

-- equivalent explicit form
SELECT * FROM orders o JOIN customers c
  ON o.customer_id = c.customer_id;

-- band join: place each value into a bracket
SELECT s.amount, b.label
  FROM sales s
  JOIN brackets b
    ON s.amount >= b.lo AND s.amount < b.hi;

-- temporal: which price was in effect at the time of sale
SELECT s.id, p.price
  FROM sales s
  JOIN prices p
    ON p.sku = s.sku
   AND s.sold_at >= p.valid_from
   AND s.sold_at <  p.valid_to;

-- overlap detection: the classic interval test
SELECT a.id, b.id
  FROM bookings a
  JOIN bookings b
    ON a.room = b.room
   AND a.id < b.id                  -- each pair once, not twice
AND a.starts < b.ends
   AND b.starts < a.ends;           -- ← the overlap condition