Module 17 — JOINs

1. What is a JOIN?

What it is: A JOIN combines rows from two or more tables based on a related column (usually a Primary Key and a Foreign Key). It's how you query across normalized tables — the "R" in RDBMS (Relational Database Management System).

Why we use it: In a normalized database, data is split across multiple tables to avoid duplication. JOINs let you reassemble related data for display — showing a post with its author, an order with its products, a student with their courses.

When we use it: Every time you need data from more than one table — which is most queries in a relational database. JOINs are the most important SQL concept after SELECT.

Table Relationships (The Bridge)

2. INNER JOIN — The "Matchmaker"

What it is: INNER JOIN returns only rows where there is a matching value in both tables. Rows without a match in either table are excluded from the result.

Why we use it: When you only want related data — "posts that have an author", "orders that have a product", "enrollments that link a student to a course". It's the most common JOIN type.

When we use it: In 90% of JOIN queries — whenever you need data from two related tables and only want rows that have matches in both.

SELECT posts.title, authors.name
FROM posts
INNER JOIN authors ON posts.author_id = authors.id;

Result: You see all posts that have a valid author. Posts without authors are hidden.

3. LEFT JOIN — The "Inclusive" Join

What it is: LEFT JOIN returns all rows from the left table (the one after FROM), and matching rows from the right table. If there's no match, the right table's columns are filled with NULL.

Why we use it: When you want to see all records from one table, even if they don't have related data — "all users and their orders, including users who haven't ordered", "all courses and their enrollments, including empty courses".

When we use it: When finding orphaned records (no match), when you need a complete list regardless of matches, or when the left table is the "primary" dataset.

SELECT authors.name, posts.title
FROM authors
LEFT JOIN posts ON authors.id = posts.author_id;

Result: You see every author. Authors with no posts show NULL for the post title.

4. RIGHT JOIN

What it is: RIGHT JOIN is the mirror of LEFT JOIN — it returns all rows from the right table, and matching rows from the left table. If there's no match, the left table's columns are NULL.

Why we use it: In practice, RIGHT JOIN is rarely used. Most developers just rewrite it as a LEFT JOIN by swapping the table order. It's included here for completeness.

When we use it: When the right table is the "primary" dataset and you want all its rows regardless of matches. Most developers prefer LEFT JOIN for readability.

SELECT authors.name, posts.title
FROM authors
RIGHT JOIN posts ON authors.id = posts.author_id;

5. FULL OUTER JOIN

What it is: FULL OUTER JOIN returns all rows from both tables. Where there's a match, the rows are combined. Where there's no match, the missing side is filled with NULL.

Why we use it: When you need a complete picture of both tables — data audits, finding unmatched records on both sides, or merging datasets that may not fully overlap.

When we use it: In data reconciliation, audits, or when merging two datasets that may have records the other doesn't.

SELECT authors.name, posts.title
FROM authors
FULL OUTER JOIN posts ON authors.id = posts.author_id;

6. Multi-Table JOINs

What it is: You can chain multiple JOINs to combine data from 3, 4, or more tables in a single query. Each JOIN adds another table to the result, linked by their relationships.

Why we use it: Real applications have deeply connected data — a post has an author, a category, tags, and comments. Multi-table JOINs let you fetch all this related data in one query.

When we use it: In complex queries that need data from multiple related tables — dashboards, reports, API responses that include nested resources.

-- Post Title + Author Name + Category Name
SELECT
    posts.title,
    authors.name AS author,
    categories.name AS category
FROM posts
JOIN authors ON posts.author_id = authors.id
JOIN categories ON posts.category_id = categories.id;

7. Visual Guide to JOINs

Common Mistakes to Avoid

1. Forgetting the ON clause — Results in a CROSS JOIN (every combination)
2. Ambiguous column names — Use table.column when column names exist in multiple tables
3. Too many JOINs — Each JOIN makes the query slower. Only join what you need.

Practice Exercise

Using your courses and students tables:

1. Create an enrollments table linking students and courses
2. Write an INNER JOIN to show each enrollment with student name and course title
3. Write a LEFT JOIN to show all courses, even those with no enrollments
4. Write a query that joins 3 tables: students, enrollments, and courses
5. Find courses that have zero enrollments using LEFT JOIN + IS NULL