Joins and CTEs

Introduction

Real apps split data across multiple tables. JOIN combines rows on keys; CTEs (WITH) name subqueries for readable multi-step SQL. This chapter covers INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins, UNION, and common table expressions—including a WITH RECURSIVE preview. Compare with MySQL joins; PostgreSQL's native FULL OUTER JOIN is a standout difference.

Prerequisites

• SELECT Queries Basics
• Insert and COPY sample users and posts

Optional Setup: Categories

Many-to-many tagging with a junction table:

INNER JOIN

Rows where the join condition matches both sides:

SELECT
  p.id,
  p.title,
  u.display_name AS author
FROM posts p
INNER JOIN users u ON u.id = p.user_id;

Only published posts:

SELECT p.title, u.email
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at IS NOT NULL;

Code explanation:

• INNER JOIN drops posts with invalid user_id and users with zero posts (for this query shape)
• ON defines the match—usually foreign key = primary key

LEFT JOIN

All rows from the left table; NULL on the right when no match:

SELECT
  u.display_name,
  p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id;

Users who never posted:

SELECT u.id, u.display_name
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE p.id IS NULL;

Code explanation:

• WHERE p.id IS NULL after LEFT JOIN finds rows with no matching post

RIGHT JOIN

Mirror of LEFT—keep all rows from the right table:

SELECT u.display_name, p.title
FROM users u
RIGHT JOIN posts p ON p.user_id = u.id;

Rare in practice—swap table order and use LEFT JOIN for readability.

FULL OUTER JOIN

PostgreSQL supports FULL OUTER JOIN natively—MySQL does not.

All users and all posts, matched where possible:

SELECT
  u.display_name,
  p.title
FROM users u
FULL OUTER JOIN posts p ON p.user_id = u.id;

Code explanation:

• Unmatched users show NULL title
• Orphan posts (invalid user_id) show NULL display_name
• Useful for data-quality audits comparing two sides

CROSS JOIN

Cartesian product—every left row paired with every right row:

SELECT u.display_name, c.name AS category
FROM users u
CROSS JOIN categories c;

Use deliberately on small sets; missing ON by accident can explode row counts.

Self Join

One table joined to itself with different aliases:

-- Example if users had manager_id REFERENCES users(id):
-- SELECT e.display_name AS employee, m.display_name AS manager
-- FROM users e
-- LEFT JOIN users m ON m.id = e.manager_id;

Two aliases on the same physical table—common for hierarchies and duplicate detection.

Many-to-Many Join

Posts with category names through post_categories:

SELECT
  p.title,
  c.name AS category
FROM posts p
INNER JOIN post_categories pc ON pc.post_id = p.id
INNER JOIN categories c ON c.id = pc.category_id
ORDER BY p.title, c.name;

UNION and UNION ALL

Combine result sets with the same column count and compatible types:

SELECT email AS contact FROM users
UNION
SELECT 'post-' || id::text AS contact FROM posts;

UNION removes duplicates; UNION ALL keeps them and is faster:

SELECT id, title FROM posts WHERE user_id = 1
UNION ALL
SELECT id, title FROM posts WHERE user_id = 2;

NATURAL JOIN (Avoid)

NATURAL JOIN matches all same-named columns—breaks when schemas change. Prefer explicit JOIN ... ON.

Common Table Expressions (WITH)

Name a subquery at the top, reference it below:

WITH published AS (
  SELECT id, user_id, title, published_at
  FROM posts
  WHERE published_at IS NOT NULL
)
SELECT u.display_name, p.title, p.published_at
FROM published p
INNER JOIN users u ON u.id = p.user_id
ORDER BY p.published_at DESC;

Chained CTEs:

Code explanation:

• CTEs improve readability—they are not always a performance win vs inline subqueries
• PostgreSQL 12+ can inline CTEs when beneficial; use WITH ... AS MATERIALIZED to force materialization when needed (advanced)

Compare subquery patterns with MySQL subqueries—PostgreSQL WITH is first-class and widely used.

WITH RECURSIVE (Preview)

Walk a tree—example: numbered steps (stand-in for org chart):

WITH RECURSIVE steps AS (
  -- Base case
  SELECT 1 AS n, 'start'::text AS label
  UNION ALL
  -- Recursive case
  SELECT n + 1, 'step ' || (n + 1)::text
  FROM steps
  WHERE n < 5
)
SELECT n, label FROM steps;

Real hierarchies use parent_id self-references—same pattern: base rows + recursive join. Deep examples appear in reporting chapters and official docs.

Join Diagram Summary

INNER:       only overlap
LEFT:        all left + matching right
RIGHT:       all right + matching left
FULL OUTER:  all from both sides
CROSS:       every combination

FAQ

JOIN vs subquery vs CTE?

Often equivalent—the planner picks a plan. Choose what reads clearest; add indexes when EXPLAIN ANALYZE shows slow scans—Indexes and EXPLAIN ANALYZE.

Why FULL OUTER JOIN?

Data reconciliation, ETL comparisons, and reports that must show unmatched rows on both sides—without UNION workarounds.

ON vs WHERE?

ON defines the join condition; WHERE filters the result. For LEFT JOIN, predicates on the right table in WHERE can turn the join into an inner join effect—put right-side filters in ON when you need to keep unmatched left rows.

Does PostgreSQL have implicit comma joins?

Yes—FROM posts p, users u WHERE ... works but avoid it; explicit JOIN prevents forgotten WHERE clauses.

CTE vs temporary table?

CTEs live for one statement. CREATE TEMP TABLE persists for the session—better for repeated heavy steps in complex scripts.

Many-to-many pattern?

Junction table with composite primary key—post_categories linking posts and categories.