Joins — Combining Tables

Why Joins?

Relational databases split data across multiple tables to avoid repetition. An employees table doesn't repeat department details on every row — it stores a department_id that links to a separate departments table.

Joins reconnect that data at query time.

Without joins, you'd have to run two queries and manually match the results. Joins do this efficiently inside the database.

Sample Tables

CREATE TABLE departments (
    id      INTEGER PRIMARY KEY,
    name    TEXT    NOT NULL,
    budget  REAL    NOT NULL
);

CREATE TABLE employees (
    id            INTEGER PRIMARY KEY,
    name          TEXT    NOT NULL,
    department_id INTEGER,     -- FK to departments
    salary        REAL    NOT NULL
);

CREATE TABLE projects (
    id          INTEGER PRIMARY KEY,
    title       TEXT    NOT NULL,
    dept_id     INTEGER,        -- FK to departments
    budget      REAL
);

-- Departments
INSERT INTO departments VALUES (1, 'Finance',    500000);
INSERT INTO departments VALUES (2, 'Technology', 750000);
INSERT INTO departments VALUES (3, 'Marketing',  300000);
INSERT INTO departments VALUES (4, 'Legal',      200000);  -- no employees

-- Employees
INSERT INTO employees VALUES (1, 'Priya Sharma', 1, 75000);
INSERT INTO employees VALUES (2, 'Raj Patel',    2, 92000);
INSERT INTO employees VALUES (3, 'Meera Singh',  3, 68000);
INSERT INTO employees VALUES (4, 'Arjun Nair',   1, 81000);
INSERT INTO employees VALUES (5, 'Sunita Rao',   2, 88000);
INSERT INTO employees VALUES (6, 'Dev Kumar',    NULL, 72000);  -- no department

-- Projects
INSERT INTO projects VALUES (1, 'Budget Recon',  1, 50000);
INSERT INTO projects VALUES (2, 'App Rebuild',   2, 200000);
INSERT INTO projects VALUES (3, 'Brand Refresh', 3, 80000);
INSERT INTO projects VALUES (4, 'Market Study',  3, 45000);

INNER JOIN

Returns only rows where the join condition is met in both tables. Rows with no match in either table are excluded.

SELECT
    e.name        AS employee,
    d.name        AS department,
    e.salary,
    d.budget
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Result:

employee     | department | salary | budget
-------------+------------+--------+--------
Priya Sharma | Finance    | 75000  | 500000
Raj Patel    | Technology | 92000  | 750000
Meera Singh  | Marketing  | 68000  | 300000
Arjun Nair   | Finance    | 81000  | 500000
Sunita Rao   | Technology | 88000  | 750000

Notice:

• Dev Kumar is excluded (department_id is NULL — no match)
• The Legal department is excluded (no employees)

Table Aliases

When joining, always alias tables to keep queries readable:

FROM employees e        -- 'e' is the alias for employees
INNER JOIN departments d ON e.department_id = d.id

Then reference columns as e.salary, d.name — no ambiguity.

JOIN is shorthand for INNER JOIN

JOIN departments d ON ...      -- same as INNER JOIN
INNER JOIN departments d ON ... -- explicit form (preferred for clarity)

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right table. If there's no match, the right table columns are NULL.

SELECT
    e.name        AS employee,
    d.name        AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Result:

employee     | department
-------------+------------
Priya Sharma | Finance
Raj Patel    | Technology
Meera Singh  | Marketing
Arjun Nair   | Finance
Sunita Rao   | Technology
Dev Kumar    | NULL         ← no matching department

Dev Kumar now appears — but with NULL for department (no match in departments).

Use Case: Find Unmatched Rows

LEFT JOIN + WHERE IS NULL is the standard way to find rows with no match:

-- Employees with no department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

Result: Dev Kumar

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, plus matching rows from the left. Less common — a RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping table order.

SELECT
    e.name        AS employee,
    d.name        AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Result:

employee     | department
-------------+------------
Priya Sharma | Finance
Arjun Nair   | Finance
Raj Patel    | Technology
Sunita Rao   | Technology
Meera Singh  | Marketing
NULL         | Legal        ← department with no employees

The Legal department now appears — but with NULL for employee.

Find Departments with No Employees

SELECT d.name AS empty_department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

Or equivalently using LEFT JOIN with tables swapped:

SELECT d.name AS empty_department
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL;

FULL OUTER JOIN

Returns all rows from both tables. NULLs fill in where there's no match on either side. Not supported in SQLite — use UNION of LEFT and RIGHT JOINs.

-- PostgreSQL / MySQL
SELECT
    e.name   AS employee,
    d.name   AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Result:

employee     | department
-------------+------------
Priya Sharma | Finance
Arjun Nair   | Finance
Raj Patel    | Technology
Sunita Rao   | Technology
Meera Singh  | Marketing
Dev Kumar    | NULL         ← employee with no dept
NULL         | Legal        ← dept with no employees

SQLite workaround

SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;

CROSS JOIN

Returns the Cartesian product — every row in the left table paired with every row in the right table. Use with care — it can produce huge result sets.

-- 5 employees × 4 departments = 20 rows
SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;

Use case: generate all combinations (e.g., all possible employee-department assignments).

Self Join

A self join joins a table to itself. Classic use: employee-manager relationships where both manager and employee are in the same table.

CREATE TABLE staff (
    id         INTEGER PRIMARY KEY,
    name       TEXT,
    manager_id INTEGER  -- FK to staff.id
);

INSERT INTO staff VALUES (1, 'Arjun Nair',   NULL);   -- CEO
INSERT INTO staff VALUES (2, 'Priya Sharma', 1);       -- reports to Arjun
INSERT INTO staff VALUES (3, 'Raj Patel',    1);       -- reports to Arjun
INSERT INTO staff VALUES (4, 'Meera Singh',  2);       -- reports to Priya

-- Show employee + their manager
SELECT
    e.name    AS employee,
    m.name    AS manager
FROM staff e
LEFT JOIN staff m ON e.manager_id = m.id;

Result:

employee     | manager
-------------+-------------
Arjun Nair   | NULL          ← no manager (CEO)
Priya Sharma | Arjun Nair
Raj Patel    | Arjun Nair
Meera Singh  | Priya Sharma

Joining Multiple Tables

You can chain multiple JOINs:

SELECT
    e.name        AS employee,
    d.name        AS department,
    p.title       AS project,
    p.budget      AS project_budget
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN projects p    ON p.dept_id = d.id;

This links employees → departments → projects.

Visual Summary of JOIN Types

Table A (employees)    Table B (departments)

INNER JOIN:  only matching rows from both A and B
LEFT JOIN:   all of A + matching B (NULLs where no B match)
RIGHT JOIN:  all of B + matching A (NULLs where no A match)
FULL JOIN:   all of A and all of B (NULLs where no match on either side)
CROSS JOIN:  every row of A × every row of B

Practical Examples

Example 1: Salary vs Department Budget Ratio

SELECT
    e.name,
    d.name                            AS dept,
    e.salary,
    d.budget,
    ROUND(e.salary / d.budget * 100, 2) AS salary_pct_of_budget
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
ORDER BY salary_pct_of_budget DESC;

Example 2: Departments and Their Projects

SELECT
    d.name        AS department,
    COUNT(p.id)   AS project_count,
    SUM(p.budget) AS total_project_budget
FROM departments d
LEFT JOIN projects p ON d.id = p.dept_id
GROUP BY d.name
ORDER BY project_count DESC;

Example 3: All Employees and Projects in Same Department

SELECT
    e.name  AS employee,
    p.title AS project
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN projects p    ON p.dept_id = d.id
ORDER BY e.name;

Common Mistakes

1. Forgetting the ON condition

-- Wrong — acts like CROSS JOIN (every row × every row)
SELECT e.name, d.name FROM employees e, departments d;

-- Correct
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

2. Ambiguous column reference

-- Wrong — 'id' exists in both tables
SELECT id, name FROM employees INNER JOIN departments ON department_id = departments.id;

-- Correct — prefix columns with table alias
SELECT e.id, e.name, d.id AS dept_id, d.name AS dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

3. Checking the wrong table for NULL in anti-join

-- Find employees with no department
-- Wrong — checking the wrong column
LEFT JOIN departments d ON ...
WHERE e.department_id IS NULL;   -- this only finds NULL FK, not "no match"

-- Correct — check the joined table's PK for NULL
LEFT JOIN departments d ON ...
WHERE d.id IS NULL;              -- NULL means "no row joined from departments"

Practice Exercises

1. List all employees along with their department name (use INNER JOIN).
2. Show all departments including those with no employees (use LEFT or RIGHT JOIN).
3. Find employees who have no department assigned.
4. List all projects along with the department name that owns them.
5. Write a self join to display each staff member alongside their manager's name.

Summary

In this chapter you learned:

• INNER JOIN — only matching rows from both tables
• LEFT JOIN — all rows from left, matching from right (NULLs where no match)
• RIGHT JOIN — all rows from right, matching from left (NULLs where no match)
• FULL OUTER JOIN — all rows from both sides
• CROSS JOIN — every combination of rows (Cartesian product)
• Self join — joining a table to itself (e.g., employee-manager hierarchy)
• Always alias tables when joining; prefix column names to avoid ambiguity
• LEFT JOIN + WHERE right_table.id IS NULL = anti-join (find unmatched rows)
• Chain multiple JOINs to combine three or more tables

Next up: Subqueries — embed one query inside another to answer complex questions.