SQL Fundamentals — Joins, Aggregations, Subqueries & CTEs

Concrete Example

One running scenario — merchants, their transactions, and their settlements — carries through all four parts below, because joins, aggregation, subqueries, and CTEs are really four different questions asked about the same shape of data.

Joins — the ON-vs-WHERE trap on an outer join

Take: "settlement total per merchant for June, including merchants with no June settlements at all." That "including the zeroes" requirement forces a LEFT JOIN, and where the date predicate goes decides whether it actually works.

-- CORRECT: the date test is part of "what counts as a match"
SELECT m.name, COALESCE(SUM(s.amount), 0) AS june_total
FROM merchants m
LEFT JOIN settlements s
       ON s.merchant_id = m.merchant_id
      AND s.settled_on >= DATE '2025-06-01'
      AND s.settled_on <  DATE '2025-07-01'
GROUP BY m.name;

-- WRONG: silently an INNER JOIN
SELECT m.name, COALESCE(SUM(s.amount), 0) AS june_total
FROM merchants m
LEFT JOIN settlements s ON s.merchant_id = m.merchant_id
WHERE s.settled_on >= DATE '2025-06-01'
  AND s.settled_on <  DATE '2025-07-01'
GROUP BY m.name;

The second query does pad in NULL rows for merchants with zero June settlements — then WHERE runs and tests NULL >= '2025-06-01', which is UNKNOWN, so every padded row is thrown away. The LEFT keyword is still sitting on the page, and it has been completely neutralised. Rule: a predicate on the nullable (inner) side of an outer join belongs in ON; a predicate on the preserved side belongs in WHERE.

Aggregation — five counts in one pass

Conditional aggregation turns rows into columns without a pivot table, and is the highest-value aggregation pattern for real reporting work:

SELECT merchant_id,
       COUNT(*)                                    AS total,
       COUNT(*) FILTER (WHERE status = 'SUCCESS')  AS succeeded,
       COUNT(*) FILTER (WHERE status = 'FAILED')   AS failed,
       SUM(amount) FILTER (WHERE method = 'UPI')   AS upi_volume,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'SUCCESS')
                   / NULLIF(COUNT(*), 0), 2)       AS success_pct
FROM transactions
GROUP BY merchant_id;

NULLIF(COUNT(*), 0) converts a 0 denominator to NULL, so the division returns NULL instead of erroring — a guard worth adding to every percentage computed in SQL.

Subqueries — NOT IN vs NOT EXISTS

"Customers who have never transacted" looks like a textbook NOT IN, and fails silently the moment the referenced column can hold a NULL:

-- Returns ZERO ROWS if transactions.customer_id contains even one NULL
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM transactions);

-- Correct, and usually the plan the first query should have gotten anyway
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM transactions t WHERE t.customer_id = c.customer_id);

NOT EXISTS asks a cardinality question — did any row come back? — which has exactly two answers. UNKNOWN has nowhere to enter. NOT IN asks a value-comparison question, and one stray NULL in the subquery's result poisons every row.

CTEs — naming a two-step pipeline

The top-3-merchants-per-month report reads as a pipeline instead of a pyramid of nested subqueries:

WITH monthly AS (
    SELECT merchant_id,
           date_trunc('month', created_at) AS month,
           SUM(amount) AS volume
    FROM transactions
    WHERE status = 'SUCCESS'
    GROUP BY 1, 2
),
ranked AS (
    SELECT *, RANK() OVER (PARTITION BY month ORDER BY volume DESC) AS rk
    FROM monthly
)
SELECT month, merchant_id, volume
FROM ranked
WHERE rk <= 3
ORDER BY month, rk;

Each named step reads top to bottom. Note that WHERE rk <= 3 is legal here only because rk is a plain output column of the ranked CTE by the time the outer query's WHERE runs against it — rk is not filtered inside the same SELECT that computed it.

Visual Model

Here's the mental model worth keeping: a query is a small pipeline, and the order you type its clauses in has almost nothing to do with the order the database runs them in. Step through the trace below. The query on the left never changes — only which line is currently executing, and what that does to the data on the right.

SELECT merchant_id, COUNT(*) AS n, AVG(amount) AS avg_amt
FROM transactions
WHERE status = 'SUCCESS'
GROUP BY merchant_id
HAVING COUNT(*) > 1
ORDER BY avg_amt DESC;