Advanced JOIN Patterns

-- SEMI-JOIN: keep rows in A that HAVE at least one match in B.
-- "Which customers have placed at least one order?"

-- EXISTS runs the inner query for each customer and stops
-- at the FIRST matching order — it never multiplies rows.
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1                       -- the 1 is a placeholder; EXISTS
    FROM orders o                  -- only cares IF a row comes back,
    WHERE o.customer_id = c.id     -- not WHAT it contains
);

-- Result:
...

-- Same answer with IN: keep customers whose id appears
-- in the list of customer_ids that have orders.
SELECT id, name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- IN builds the whole list first; EXISTS can short-circuit.
-- On big tables EXISTS is usually the faster of the two.

-- ANTI-JOIN: keep rows in A that have NO match in B.
-- "Which customers have NEVER placed an order?"

-- NOT EXISTS is the safe, fast, NULL-proof way to do this.
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

-- Result: just Dan (id 4) — the only customer with zero orders.

-- The SAME idea with NOT IN — but watch the NULL trap below.
SELECT id, name
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL   -- ⚠️ CRITICAL guard, see why next
);

-- Result: Dan (id 4). Identical to NOT EXISTS — WHEN the guard is present.

-- ⚠️ THE NULL TRAP — why NOT EXISTS is the safer default.
-- Imagine ONE order row has a NULL customer_id (an unassigned order):

SELECT id, name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);  -- no NULL guard!

-- Result: 0 rows. Nothing. Empty.
--
-- Why? "id NOT IN (1, 2, 3, NULL)" becomes
-- "id <> 1 AND id <> 2 AND id <> 3 AND id <> NULL".
-- "id <> NULL" is UNKNOWN (never TRUE), so the whole row is rejected.
-- One stray NULL silently wipes out every result.
-- NOT EXIS
...

-- SELF-JOIN: join a table to ITSELF using two aliases.
-- "Which customers placed more than one order, and which pair?"

-- We give 'orders' two names (o1, o2) so SQL treats it as two tables.
SELECT o1.customer_id,
       o1.id AS first_order,
       o2.id AS second_order
FROM orders o1
JOIN orders o2
    ON o1.customer_id = o2.customer_id   -- same customer...
    AND o1.id < o2.id;                   -- ...but two DIFFERENT orders

-- The "o1.id < o2.id" does two jobs:
--   • removes a row pai
...

-- CROSS JOIN: pair EVERY row of A with EVERY row of B.
-- Also called a Cartesian product. There is no ON clause.
SELECT c.name, o.id AS order_id
FROM customers c
CROSS JOIN orders o;

-- 5 customers × 5 orders = 25 rows (every name beside every order).
-- ⚠️ This grows fast: 1,000 × 1,000 = 1,000,000 rows.
-- Use CROSS JOIN deliberately — to build combinations or fill gaps,
-- never by accident (a missing JOIN condition becomes a CROSS JOIN).

-- LATERAL: run a subquery FOR EACH row of the outer table,
-- and let that subquery SEE the outer row's columns.
-- "Get each customer's single biggest order."

SELECT c.name, top_order.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.amount
    FROM orders o
    WHERE o.customer_id = c.id     -- ← references the OUTER c.id (that's LATERAL)
    ORDER BY o.amount DESC
    LIMIT 1
) AS top_order;

-- CROSS JOIN LATERAL drops customers whose subquery is empty,
-- so Dan (no orders) does N
...