Advanced Joins & Set Operations

FULL OUTER JOIN

Returns every row from both tables. NULLs fill the missing side.

-- Reconcile two daily snapshots — find new, closed, and changed accounts
WITH comparison AS (
    SELECT
        COALESCE(t.account_id, y.account_id) AS account_id,
        y.balance  AS yesterday_balance,
        t.balance  AS today_balance,
        CASE
            WHEN y.account_id IS NULL      THEN 'NEW'
            WHEN t.account_id IS NULL      THEN 'CLOSED'
            WHEN t.balance <> y.balance     THEN 'CHANGED'
            ELSE                                 'UNCHANGED'
        END AS status
    FROM   accounts_today     t
    FULL OUTER JOIN accounts_yesterday y ON t.account_id = y.account_id
)
SELECT status, COUNT(*) AS count
FROM   comparison GROUP BY status;

CROSS JOIN

Every row from the left × every row from the right. No join condition. If left has 5 rows and right has 4 — result is 20 rows.

-- Generate a date × stock grid to find missing trading days
WITH all_combos AS (
    SELECT d.trade_date, s.symbol
    FROM   (SELECT DISTINCT trade_date FROM daily_stock) d
    CROSS JOIN (SELECT DISTINCT symbol FROM daily_stock) s
)
SELECT ac.symbol, ac.trade_date AS missing_date
FROM   all_combos ac
LEFT JOIN daily_stock ds
    ON ac.symbol = ds.symbol AND ac.trade_date = ds.trade_date
WHERE  ds.symbol IS NULL
ORDER BY ac.symbol, ac.trade_date;

SELF JOIN

A table joined to itself using two different aliases. Used for hierarchies and same-table comparisons.

-- Employee with their manager's name and salary gap
SELECT e.name                    AS employee,
       e.salary,
       m.name                    AS manager,
       e.salary - m.salary       AS salary_diff
FROM   employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- Stocks that closed within 1% of each other on different days
SELECT s1.trade_date, s2.trade_date AS date2, s1.close, s2.close
FROM   daily_stock s1
JOIN   daily_stock s2
    ON  s1.symbol = s2.symbol
    AND s1.trade_date < s2.trade_date       -- avoid duplicates
    AND ABS(s1.close - s2.close) / s1.close < 0.01
WHERE  s1.symbol = 'HDFC';

Non-equi JOIN

Join on a range or condition instead of equality. Used for tax slabs, risk bands, and date ranges in BFSI.

-- Assign each transaction to a risk band
-- risk_bands: band_name | min_amount | max_amount
SELECT t.txn_id, t.amount, r.band_name
FROM   transactions t
JOIN   risk_bands   r
    ON t.amount BETWEEN r.min_amount AND r.max_amount;

-- Assign transactions to financial quarters
SELECT t.txn_id, t.txn_date, q.q_name
FROM   transactions t
JOIN   quarters     q
    ON t.txn_date BETWEEN q.q_start AND q.q_end
WHERE  t.status = 'SUCCESS';

LATERAL join (PostgreSQL) / CROSS APPLY (Oracle)

-- PostgreSQL: last 3 transactions per account
SELECT a.account_id, a.customer, t.txn_id, t.amount
FROM   accounts a
CROSS JOIN LATERAL (
    SELECT txn_id, amount
    FROM   transactions
    WHERE  account_id = a.account_id  -- references outer row
    ORDER BY txn_date DESC
    LIMIT 3
) t;

-- Oracle equivalent
CROSS APPLY (
    SELECT txn_id, amount FROM transactions
    WHERE  account_id = a.account_id
    ORDER BY txn_date DESC
    FETCH FIRST 3 ROWS ONLY
) t

Set operations

-- Accounts active only in 2023 (not 2024)
SELECT account_id FROM transactions WHERE EXTRACT(YEAR FROM txn_date)=2023
MINUS                          -- Oracle | use EXCEPT in PostgreSQL
SELECT account_id FROM transactions WHERE EXTRACT(YEAR FROM txn_date)=2024;

-- Delta detection: new records in staging not in production
SELECT txn_id, account_id, amount FROM transactions_staging
EXCEPT
SELECT txn_id, account_id, amount FROM transactions_production;