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:

-- All customers, even those without orders
SELECT
  c.id,
  c.name,
  COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

---

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror of LEFT JOIN. All rows from the right table are preserved:

-- All products, even unassigned ones
SELECT
  p.name AS product_name,
  c.name AS category_name
FROM categories c
RIGHT JOIN products p ON p.category_id = c.id;

Tip: RIGHT JOINs are rarely used in practice. You can always rewrite them as LEFT JOINs by swapping the table order.

---

FULL OUTER JOIN

Returns all rows from both tables. Where there is no match, the missing side is NULL:

-- Find unmatched records between two systems
SELECT
  a.employee_id AS hr_id,
  b.employee_id AS payroll_id,
  COALESCE(a.name, b.name) AS employee_name
FROM hr_employees a
FULL OUTER JOIN payroll_employees b ON a.employee_id = b.employee_id
WHERE a.employee_id IS NULL OR b.employee_id IS NULL;

---

CROSS JOIN

Produces the Cartesian product — every combination of rows from both tables:

-- Generate all possible size-colour combinations
SELECT
  s.size_label,
  c.colour_name
FROM sizes s
CROSS JOIN colours c;

Warning: A CROSS JOIN between a table with 1,000 rows and another with 1,000 rows produces 1,000,000 rows. Use with care.

---

Self-Joins

Join a table to itself. Common for hierarchical data or comparing rows within the same table:

-- Employee reporting hierarchy
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- Find duplicate emails
SELECT
  a.id AS id_1,
  b.id AS id_2,
  a.email
FROM users a
JOIN users b ON a.email = b.email AND a.id < b.id;

---

LATERAL Joins

A LATERAL join lets the subquery on the right reference columns from the left side. Think of it as a "for-each" loop in SQL: