JOINs: All Types

🎯Learning Objectives

• Explain the difference between INNER, LEFT, RIGHT, and FULL OUTER JOIN
• Write multi-table JOIN queries using the DVD Rental schema
• Use SELF JOIN to relate rows within the same table
• Combine result sets with UNION and UNION ALL
• Choose the correct join type for a given problem

📖Theory

1. Why JOINs Exist

Relational databases store data across multiple tables to avoid duplication. A film row does not embed category or actor data — it holds foreign keys pointing to those tables. JOINs reassemble that data at query time.

Every JOIN follows the same basic pattern:

SELECT columns
FROM table_a
JOIN table_b ON table_a.key = table_b.key;

The ON clause defines the match condition. Rows that satisfy the condition are combined into a single output row.

2. INNER JOIN

INNER JOIN returns only rows where the condition matches in both tables. Rows with no counterpart in the other table are dropped entirely.

-- All films that have at least one actor assigned
SELECT f.title, a.first_name, a.last_name
FROM film f
INNER JOIN film_actor fa ON f.film_id = fa.film_id
INNER JOIN actor a ON fa.actor_id = a.actor_id
ORDER BY f.title
LIMIT 10;

Think of INNER JOIN as the intersection of two sets: only the overlap survives.

3. LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN keeps all rows from the left table. For each left row, PostgreSQL looks for a matching right row. If a match is found, the right columns are filled in. If no match exists, the right columns are NULL.

-- All customers, plus their rental if one exists (NULL if not)
SELECT c.first_name, c.last_name, r.rental_id
FROM customer c
LEFT JOIN rental r ON c.customer_id = r.customer_id
LIMIT 10;

The classic use case is finding rows with no counterpart in another table — by filtering for WHERE right_table.key IS NULL.

4. RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN is the mirror of LEFT JOIN — all rows from the right table are preserved, and left-side columns go NULL when there is no match. In practice, most developers rewrite RIGHT JOINs as LEFT JOINs by swapping table order, because LEFT JOIN is more natural to read.

-- All staff, plus their store (even if store record is missing)
SELECT st.first_name, st.last_name, s.address_id
FROM store s
RIGHT JOIN staff st ON s.store_id = st.store_id;

5. FULL OUTER JOIN

FULL OUTER JOIN returns every row from both tables. Where a match exists, the columns are combined. Where no match exists on either side, the missing columns are NULL. It is the union of LEFT JOIN and RIGHT JOIN.

-- Every film and every inventory item; NULLs show gaps on either side
SELECT f.title, i.inventory_id, i.store_id
FROM film f
FULL OUTER JOIN inventory i ON f.film_id = i.film_id
ORDER BY f.film_id
LIMIT 20;

FULL OUTER JOIN is relatively rare but useful for reconciliation — finding rows in table A that have no match in B, and vice versa, all in one query.

6. SELF JOIN

A SELF JOIN joins a table to itself. PostgreSQL has no special syntax for this — you just reference the same table twice with different aliases. Self joins are used when rows within a table have relationships to other rows in the same table.

-- Pairs of customers who live in the same city
SELECT c1.first_name AS customer1,
       c2.first_name AS customer2,
       ci.city
FROM customer c1
JOIN address a1 ON c1.address_id = a1.address_id
JOIN customer c2 ON c1.customer_id < c2.customer_id
JOIN address a2 ON c2.address_id = a2.address_id
JOIN city ci    ON a1.city_id = ci.city_id
WHERE a1.city_id = a2.city_id
LIMIT 10;

The condition c1.customer_id < c2.customer_id prevents duplicate pairs (A+B and B+A) and self-matches (A+A).

7. UNION and UNION ALL

UNION combines the result sets of two or more SELECT statements into a single list. The rules are:

• Both queries must return the same number of columns
• Corresponding columns must have compatible data types
• UNION removes duplicate rows (like SELECT DISTINCT)
• UNION ALL keeps all rows including duplicates — and is faster

-- Combined name list from actors and customers
SELECT first_name, last_name, 'actor'    AS source FROM actor
UNION
SELECT first_name, last_name, 'customer' AS source FROM customer
ORDER BY last_name;

8. Choosing the Right Join

💻Code Examples

Example A — Actors and Their Films (INNER JOIN, three tables)

SELECT a.first_name,
       a.last_name,
       f.title,
       f.release_year
FROM actor a
INNER JOIN film_actor fa ON a.actor_id = fa.actor_id
INNER JOIN film f        ON fa.film_id  = f.film_id
ORDER BY a.last_name, a.first_name
LIMIT 20;

This chains two INNER JOINs through the film_actor bridge table. Every actor-film combination is returned; actors with no films and films with no actors are excluded.

Example B — Customers With No Rentals (LEFT JOIN + NULL filter)

SELECT c.customer_id,
       c.first_name,
       c.last_name,
       c.email
FROM customer c
LEFT JOIN rental r ON c.customer_id = r.customer_id
WHERE r.rental_id IS NULL
ORDER BY c.last_name;

LEFT JOIN ensures every customer row survives. The WHERE r.rental_id IS NULL filter then keeps only those rows where no rental matched — i.e., customers who have never rented anything.

Example C — Combined Name Directory (UNION)

SELECT first_name,
       last_name,
       'Actor'    AS role
FROM actor
UNION
SELECT first_name,
       last_name,
       'Customer' AS role
FROM customer
UNION
SELECT first_name,
       last_name,
       'Staff'    AS role
FROM staff
ORDER BY last_name, first_name;

Three tables are combined into one alphabetical directory. UNION removes any rows where the same name appears as both, for example, an actor and a customer with identical names.

✏️Practice Tasks

SELECT f.title, l.name AS language_name
FROM film f
INNER JOIN language l ON f.language_id = l.language_id
ORDER BY f.title;

SELECT f.title, f.rental_rate
FROM film f
LEFT JOIN inventory i  ON f.film_id       = i.film_id
LEFT JOIN rental r     ON i.inventory_id  = r.inventory_id
WHERE r.rental_id IS NULL
ORDER BY f.rental_rate DESC;

SELECT s.first_name,
       s.last_name,
       COALESCE(SUM(p.amount), 0) AS total_amount
FROM staff s
LEFT JOIN payment p ON s.staff_id = p.staff_id
GROUP BY s.staff_id, s.first_name, s.last_name

UNION ALL

SELECT 'TOTAL', '', SUM(amount)
FROM payment

ORDER BY total_amount;

⚠️Common Mistakes

Forgetting the ON clause condition entirely. Writing FROM film JOIN inventory without ON film.film_id = inventory.film_id produces a CROSS JOIN — every film row combined with every inventory row. On large tables this can generate millions of rows.

Using INNER JOIN when LEFT JOIN is needed. If the goal is to find customers with no rentals, INNER JOIN silently drops those customers from the result. Always ask: "Do I need rows even when there is no match?"

NULL comparisons after LEFT JOIN. After a LEFT JOIN, unmatched rows have NULL in the right-side columns. You must use IS NULL or IS NOT NULL — comparing with = NULL always returns false in SQL.

Ambiguous column names. When two joined tables have a column with the same name (e.g., both have name), PostgreSQL raises an error. Always qualify columns with a table alias.

UNION column count mismatch. Both SELECT statements in a UNION must have exactly the same number of columns. Add literal values or NULL placeholders to balance them.

Duplicate pairs in SELF JOIN. Without table1.id < table2.id in the condition, a self join returns both (A, B) and (B, A) as separate rows, and (A, A) as a self-match.

🎓Instructor Notes

Timing breakdown:

• 0–5 min: Recap relational model and why data is split across tables
• 5–20 min: INNER JOIN live demo — actor + film_actor + film
• 20–30 min: LEFT JOIN demo — customers with no rentals; draw the Venn diagram
• 30–40 min: RIGHT JOIN (brief), FULL OUTER JOIN — show the gaps
• 40–50 min: SELF JOIN — customers in same city; emphasize the alias trick and < condition
• 50–60 min: UNION vs UNION ALL — column count rule, deduplication cost
• 60–80 min: Students work on Tasks 1–3

Live-coding cues:

• For INNER JOIN: start with two tables, then add the bridge table. Show row counts at each step with SELECT COUNT(*).
• For LEFT JOIN: first run without the WHERE filter to show NULL columns, then add the filter.
• For SELF JOIN: draw two copies of the customer table on the whiteboard before writing SQL.
• For UNION: deliberately write different column counts to trigger the error, then fix it live.

Discussion questions:

• "If I LEFT JOIN and never filter for NULL, how does the result differ from INNER JOIN?"
• "What would happen if we removed the c1.customer_id < c2.customer_id condition from the self join?"
• "Why is UNION ALL generally faster than UNION?"