LEFT, RIGHT, FULL, CROSS & SELF JOIN — SQL Course

What you'll learn

Choose outer, cross, and self joins to preserve unmatched rows, generate combinations, and traverse same-table relationships. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply LEFT JOIN to a realistic data question
Apply RIGHT and FULL JOIN to a realistic data question
Apply Anti-join pattern to a realistic data question
Apply CROSS JOIN to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
LEFT JOIN	All left rows plus matching right rows	Put right-table preservation filters in ON when unmatched left rows must remain
Anti-join	Rows with no related match	Use NOT EXISTS or LEFT JOIN plus a non-nullable right key IS NULL
SELF JOIN	A table joined to another role of itself	Use distinct aliases that describe each role

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the non-inner join question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Find authors without books

The left join preserves every author and the NULL right key identifies missing relationships.

authors_without_books.sql

SELECT a.id, a.display_name
FROM publishing.authors AS a
LEFT JOIN publishing.book_authors AS ba ON ba.author_id = a.id
WHERE ba.author_id IS NULL
ORDER BY a.display_name;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

A WHERE predicate on the nullable side of a LEFT JOIN commonly removes NULL-extended rows and silently turns it into an inner join.

Independent workshop

Build a review-ready non-inner join lab against the course commerce dataset.

Your finished workshop must include:

LEFT JOIN
RIGHT and FULL JOIN
Anti-join pattern
CROSS JOIN
SELF JOIN
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

LEFT JOIN: Put right-table preservation filters in ON when unmatched left rows must remain
Anti-join: Use NOT EXISTS or LEFT JOIN plus a non-nullable right key IS NULL
SELF JOIN: Use distinct aliases that describe each role

Quick check

1. Which rule best applies to LEFT JOIN?

2. Which rule best applies to Anti-join?

3. Which rule best applies to SELF JOIN?

Next: Multi-Table Joins & Duplicate Control