Joins Mastery

Joins are the heart of relational databases. This lesson goes beyond basic INNER and LEFT joins to cover every join type in PostgreSQL, including self-joins, lateral joins, anti-joins, and critical performance considerations.

---

Join Types Overview

Join Type	Returns
INNER JOIN	Only matching rows from both tables
LEFT JOIN	All rows from left table + matches from right
RIGHT JOIN	All rows from right table + matches from left
FULL OUTER	All rows from both tables, NULLs where no match
CROSS JOIN	Cartesian product of both tables

---

INNER JOIN

Returns only rows with a match in both tables:

SELECT
  o.order_id,
  o.order_date,
  c.name AS customer_name,
  c.email
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

Tip: INNER JOIN and JOIN are synonymous in SQL. Most style guides prefer writing INNER JOIN for clarity.

---

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right. Non-matching right-side columns are filled with NULL: