JOIN Types

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind JOIN Types
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

A JOIN combines tables through key relationships. `INNER JOIN` returns matches on both sides, while `LEFT JOIN` preserves every row from the left side. Joining multiple one-to-many relationships can multiply rows, so define the result grain before aggregating.

Connect it to a real scenario

Join tutorials to lessons to list lessons for every published tutorial. Use LEFT JOIN when tutorials with no lessons must remain visible, and avoid putting right-table filters in WHERE when that would erase unmatched rows.

INNER keeps matched rows
LEFT preserves the entire left side
Define the result grain before joining

Try the working example

sql

SELECT
  t.tutorial_id,
  t.title AS tutorial_title,
  l.lesson_number,
  l.title AS lesson_title
FROM app.tutorials AS t
LEFT JOIN app.lessons AS l
  ON l.tutorial_id = t.tutorial_id
WHERE t.is_published = true
ORDER BY t.title, l.lesson_number;

You should see

Each published tutorial appears with its lessons in a deterministic order.

5-minute try-it

LEFT JOIN users to enrollments and include users who have not enrolled in anything.

One important caution

Filtering a right-side column in WHERE after a LEFT JOIN can silently turn the result into INNER-JOIN behavior.

PostgreSQL — Joins Between Tables — PostgreSQL Global Development Group

Easy traps

Filtering a right-side column in WHERE after a LEFT JOIN can silently turn the result into INNER-JOIN behavior.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

LEFT JOIN users to enrollments and include users who have not enrolled in anything.

You'll know it worked when: Each published tutorial appears with its lessons in a deterministic order.