Joins, subqueries, and common table expressions are the main ways PostgreSQL answers questions that need more than one table or more than one logical step. The outcome of this lesson is practical: you should be able to read a query as a pipeline of row sets, predict how many rows can appear after each step, and choose the clearest shape for the question.
In the querying section of this PostgreSQL course, these tools sit between basic SELECT statements and query tuning. A join combines relations horizontally by matching rows. A subquery nests one query inside another so a filter, value, or derived table can depend on another result. A common table expression, introduced with WITH, names an intermediate result so later parts of the statement can refer to it.
How PostgreSQL Thinks About Row Sets
PostgreSQL does not execute SQL as written line by line. It parses the statement, rewrites parts where rules apply, plans possible access paths, and executes the chosen plan. Joins, subqueries, and CTEs all become plan nodes that produce rows for later nodes. The planner estimates row counts, selectivity, available indexes, and join costs, then chooses strategies such as nested loop, hash join, or merge join.
A nested loop join reads rows from one side and probes the other side for matches. It is often effective when the outer side is small and the inner side has a useful index. A hash join builds an in-memory hash table from one input and probes it with the other. It is common for equality joins over larger unsorted inputs. A merge join walks two sorted inputs together and can be efficient when matching order already exists or is cheap to create.
Subqueries have several forms. A scalar subquery returns one column from at most one row and can be used as a value. An IN, EXISTS, or comparison subquery is used as a predicate. A subquery in FROM is a derived table with its own column list. A correlated subquery references columns from the outer query; PostgreSQL may transform it into a join-like plan, but if it must run repeatedly, it can become expensive.
A CTE is written before the main query with WITH name AS (...). Modern PostgreSQL can inline many non-recursive CTEs, which lets the planner optimize them together with the outer query. A CTE can also be forced with MATERIALIZED or NOT MATERIALIZED. Materialization computes the CTE once and stores the result for reuse inside the statement, which can help when the result is reused but can hurt when outer filters would otherwise be pushed down.
Syntax Anatomy
The join clause names the right-hand relation and an ON condition. Put relationship predicates in ON, and put final row filters in WHERE. That distinction matters most with outer joins because a WHERE filter on the nullable side can accidentally remove unmatched rows.
SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE c.region = 'west';Subqueries appear where their result shape is legal. A scalar subquery must produce one value. An EXISTS subquery is normally used when the question is whether at least one matching row exists; PostgreSQL can stop once it finds a match.
SELECT c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);A CTE names a result before the main statement. Multiple CTEs are separated by commas, and later CTEs can refer to earlier ones.
WITH paid_orders AS (
SELECT id, customer_id, order_total
FROM orders
WHERE status = 'paid'
)
SELECT customer_id, sum(order_total) AS paid_total
FROM paid_orders
GROUP BY customer_id;Worked Data Set
The examples use a small sales model. The data is intentionally tiny so the result of each query can be inspected by eye. In real systems, the same reasoning applies, but you should also compare plans with EXPLAIN.
CREATE TEMP TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL,
region text NOT NULL
);
CREATE TEMP TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(id),
status text NOT NULL,
order_total numeric(10,2) NOT NULL
);
INSERT INTO customers (id, name, region) VALUES
(1, 'Ada', 'west'),
(2, 'Ben', 'east'),
(3, 'Cy', 'west');
INSERT INTO orders (id, customer_id, status, order_total) VALUES
(10, 1, 'paid', 120.00),
(11, 1, 'open', 35.00),
(12, 2, 'paid', 80.00);Example 1: Inner Join for Matching Rows
An inner join keeps only rows where the join condition succeeds. Here, customer Cy has no order, so Cy does not appear. Ada appears twice because two order rows match Ada’s one customer row. This is the first cardinality lesson: a join can multiply rows when the relationship is one-to-many.
SELECT c.name, o.id AS order_id, o.status, o.order_total
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.id
ORDER BY c.id, o.id;The deterministic result is:
name | order_id | status | order_total
------+----------+--------+-------------
Ada | 10 | paid | 120.00
Ada | 11 | open | 35.00
Ben | 12 | paid | 80.00Use an inner join when the requested entity must have a match on both sides, such as paid invoice lines with their invoice headers. If you expected one row per customer, this query shape is wrong unless you aggregate first or filter to one order.
Example 2: Left Join Without Losing Unmatched Rows
A left join keeps every row from the left table and fills right-side columns with nulls when no match exists. This query counts paid orders per west-region customer, including customers with zero paid orders. The paid-order predicate belongs in the ON clause; putting o.status = 'paid' in WHERE would remove Cy because Cy’s joined order columns are null.
SELECT c.name, count(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.status = 'paid'
WHERE c.region = 'west'
GROUP BY c.id, c.name
ORDER BY c.id;The result is:
name | paid_orders
------+-------------
Ada | 1
Cy | 0This pattern is common in reports. The design choice is whether missing right-side data means zero, unknown, or exclusion. A left join plus count(o.id) expresses zero matches. A left join plus sum(o.order_total) would return null for no matches unless you wrap it in coalesce.
Example 3: Subquery for Existence
The next query asks for customers who have at least one paid order above 100. It does not need order columns in the final output, so EXISTS describes the intent directly. PostgreSQL can stop checking a customer after the first qualifying order is found.
SELECT c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
AND o.status = 'paid'
AND o.order_total > 100
)
ORDER BY c.id;The result is one row:
name
------
AdaAn equivalent inner join can produce the same names only if you handle duplicates. If Ada had two qualifying orders, the join would produce Ada twice unless you used DISTINCT or grouped the result. EXISTS avoids that accidental multiplication.
Example 4: CTE for a Multi-Step Question
CTEs shine when a query has named stages. This example first totals paid order value per customer, then ranks customers by that total. Each CTE has a narrow responsibility, and the final select reads like the business question.
WITH paid_totals AS (
SELECT customer_id, sum(order_total) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
), ranked_customers AS (
SELECT c.name,
coalesce(p.paid_total, 0) AS paid_total,
dense_rank() OVER (ORDER BY coalesce(p.paid_total, 0) DESC) AS sales_rank
FROM customers AS c
LEFT JOIN paid_totals AS p
ON p.customer_id = c.id
)
SELECT name, paid_total, sales_rank
FROM ranked_customers
ORDER BY sales_rank, name;The result is:
name | paid_total | sales_rank
------+------------+-----------
Ada | 120.00 | 1
Ben | 80.00 | 2
Cy | 0.00 | 3The trade-off is readability versus optimization freedom. A simple CTE usually remains optimizable, but a forced materialized CTE can prevent predicate pushdown. If a report becomes slow, compare the plan for the CTE version, an inline derived-table version, and a version that aggregates before joining.
Design Choices and Trade-Offs
Choose a join when the final result needs columns from multiple relations. Choose EXISTS when you only need to know that a related row exists. Choose an aggregate subquery or CTE when you need to reduce a one-to-many relationship before joining it back to a one-row-per-entity result.
Be precise about join type. JOIN means inner join. LEFT JOIN preserves the left side. RIGHT JOIN is rarely necessary because swapping table order usually makes a left join clearer. FULL JOIN preserves unmatched rows from both sides and is useful for reconciliation queries, but it requires careful null handling. CROSS JOIN creates every combination of rows, which is correct for grids and dangerous when accidental.
Indexes should follow access patterns. Foreign key columns used in joins are strong index candidates, especially on child tables such as orders.customer_id. Predicates inside subqueries, such as status and order_total, may also need supporting indexes when data volume grows. Always validate with representative data because a useful index for one query can be irrelevant for another.
Failure Modes and Troubleshooting
Symptom: a report shows duplicate customers. Cause: a one-to-many join returned one row per matching child row. Diagnose: count rows before and after the join, then group by the supposed unique key with count(*). Correction: aggregate the child table first, use EXISTS, or define which single child row should be selected.
Symptom: a left join behaves like an inner join. Cause: a right-side filter was placed in WHERE, rejecting null-extended rows. Diagnose: temporarily select the right-side key and inspect rows where it is null. Correction: move match-specific filters into ON, or explicitly allow nulls in WHERE when that matches the requirement.
Symptom: a query that was clear with a CTE becomes slow on large data. Cause: the plan may scan or materialize more rows than needed, or estimates may be poor. Diagnose: run EXPLAIN (ANALYZE, BUFFERS) in a safe environment and compare estimated rows with actual rows. Correction: add or adjust indexes, move selective filters earlier, refresh statistics with ANALYZE, or test NOT MATERIALIZED or a derived table.
Reliability, Security, and Performance
Join mistakes are data correctness mistakes. A duplicated join can overstate revenue; a misplaced outer-join filter can hide customers with no activity. Treat row cardinality as part of the query contract. When exposing query results through an application, avoid building SQL by string concatenation. Use parameters for user-supplied values and keep table or column choices on an allowlist if they must be dynamic.
For performance, start with the shape of the result. Reduce large child tables before joining when the final output is grouped by parent. Prefer EXISTS for semi-join questions. Inspect plans when data grows, not only when syntax works. A query that is instant on three rows can be unacceptable on millions.
Hands-On Lab
Prerequisites: access to a PostgreSQL session where temporary tables are allowed, such as psql connected to a local development database. The lab only creates temporary tables, so cleanup happens automatically at session end.
- Open a new database session and run the worked data set creation script from this lesson.
- Run the inner join example and verify that Ada appears twice, Ben appears once, and Cy does not appear.
- Run the left join example and verify that Cy appears with
0paid orders. - Move
o.status = 'paid'from the left join’sONclause into theWHEREclause, rerun the query, and observe that Cy disappears. Move it back to correct the query. - Run the
EXISTSexample and confirm that only Ada is returned. - Run the CTE ranking example and confirm the rank order Ada, Ben, Cy.
- For cleanup, either close the session or run
DROP TABLE orders; DROP TABLE customers;before closing it.
Verification is the expected row set, not merely the absence of an error. If your output differs, check that the temporary tables contain exactly the inserted rows and that your filters are in the same clauses shown above.
Assessment Exercises
- A query should return every customer and the total value of paid orders, including customers with no orders. Which join type and aggregate expression would you use, and why?
- Rewrite a join that only checks whether a customer has a refund as an
EXISTSquery. What duplicate-row problem does this avoid? - You see
LEFT JOIN orders o ... WHERE o.status = 'paid'. Explain how this changes the meaning of the left join and how to fix it. - A CTE is referenced twice in a query. When might materializing it help, and when might it hurt?
- Given an unexpectedly large result set, describe the diagnostic query you would run to find which join multiplied rows.
Summary
Joins combine row sets, subqueries ask nested questions, and CTEs name intermediate results. The core skill is controlling cardinality: know which side is preserved, whether child rows multiply parent rows, and when a question is really about existence or aggregation. Use clear query shape first, then verify behavior with expected rows and inspect plans when volume makes cost matter.
