Advanced JOIN Patterns: Anti, Semi, Cross & Lateral

-- ANTI-JOIN: Find rows in A that have NO match in B
-- "Which customers have never placed an order?"

-- Method 1: NOT EXISTS (usually fastest)
SELECT c.id, c.name, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

-- Method 2: LEFT JOIN + IS NULL
SELECT c.id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- Method 3: NOT IN (caution with NULLs!)
SELECT id, name, email
FROM customers
WHERE
...

-- SEMI-JOIN: Find rows in A that HAVE a match in B
-- "Which customers HAVE placed at least one order?"
-- (but don't duplicate rows if they have multiple orders)

-- Method 1: EXISTS (best — stops at first match)
SELECT c.id, c.name, c.email
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

-- Method 2: IN
SELECT id, name, email
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- Method 3: JOIN + DISTINCT (less efficient)
SELECT DISTINCT
...

-- CROSS JOIN: Every row in A paired with every row in B
-- Also called a "Cartesian product"

-- Generate a calendar of all date + product combinations
SELECT d.date, p.product_name
FROM (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-01-07'::date,
        '1 day'::interval
    )::date AS date
) d
CROSS JOIN (
    SELECT DISTINCT product_name FROM products
    WHERE category = 'Electronics'
) p
ORDER BY d.date, p.product_name;

-- Use case: Find missing data
-- "Which pr
...

-- LATERAL JOIN: run a subquery FOR EACH row of the outer table
-- Like a "for-each loop" in SQL

-- "Get each customer's 3 most recent orders"
SELECT c.name, recent.*
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.id AS order_id, o.total, o.order_date
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.order_date DESC
    LIMIT 3
) recent;

-- Without LATERAL you'd need window functions:
SELECT name, order_id, total, order_date
FROM (
    SELECT c.name, o.id AS order_id, o.total
...

-- SELF-JOIN: join a table to itself
-- Common for hierarchies and comparisons

-- Employee-Manager hierarchy
SELECT e.name AS employee,
       m.name AS manager,
       m2.name AS skip_level_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees m2 ON m.manager_id = m2.id;

-- Find duplicate records
SELECT a.id, a.email, b.id AS duplicate_id
FROM users a
JOIN users b ON a.email = b.email AND a.id < b.id;
-- a.id < b.id prevents: (1,2) and (2,1) duplicates

-- 
...