Multiple Joins — Combining 3+ Tables

---

title: “Multiple Joins (3+ Tables)” part: 2 topic_number: 4 slug: “multiple-joins” difficulty: “Intermediate” prerequisites: “inner-join, left-join” —

Multiple Joins — Combining 3+ Tables

What Is It?

Multiple joins connect three or more tables in a single query. This lets you pull related data from across your entire database — like showing employee names, their department, and the projects they’re working on, all in one result.

Real-world analogy: Like connecting the dots in a network — following relationships from person → company → project → client.

When you’d use it:

• Complex reporting across many entities
• Denormalizing data for analysis
• Building comprehensive dashboards
• Tracking relationships across your data model

---

Syntax Breakdown

SELECT ...
FROM table1
JOIN table2 ON table1.key = table2.key
JOIN table3 ON table2.key = table3.key
JOIN table4 ON table3.key = table4.key
...
WHERE ...

Key points:

• Chain joins one after another
• Each JOIN has its own ON condition
• Can mix INNER, LEFT, RIGHT joins
• Order matters for readability (but not always for results)
• Be mindful of Cartesian explosion (result multiplying)

---

Basic Examples

Three-Table Join: Employees, Departments, Projects

SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    p.project_name,
    ep.role,
    ep.hours_allocated
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id
ORDER BY e.last_name, p.project_name
LIMIT 10;

Expected output:

first_name	last_name	department_name	project_name	role	hours_allocated
Jack	Anderson	Human Resources	CRM System	Developer	120
Jack	Anderson	Human Resources	Mobile App	Lead Developer	160
David	Brown	Finance	API Development	Developer	100
Sam	Clark	Engineering	Website Redesign	Lead Developer	150
…	…	…	…	…	…

Complete picture: who works where on what.

Four-Table Join with Manager

-- Add employee alias for manager
SELECT 
    e.first_name AS employee,
    d.department_name,
    p.project_name,
    ep.role,
    m.first_name AS manager_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY p.project_name, e.last_name
LIMIT 10;

Expected output:

employee	department_name	project_name	role	manager_name
David	Finance	API Development	Developer	Bob
Henry	Finance	API Development	Lead Developer	NULL
Jack	Human Resources	CRM System	Developer	Alice
Ivy	Human Resources	CRM System	Developer	NULL
…	…	…	…	…

Full organizational context for each project assignment.

---

Going Deeper

Mix INNER and LEFT Joins

Show all employees, their departments, and any projects (even if they have none):

SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    p.project_name,
    ep.hours_allocated
FROM employees e
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN employee_projects ep ON e.employee_id = ep.employee_id
LEFT JOIN projects p ON ep.project_id = p.project_id
ORDER BY e.last_name;

Expected output (partial):

first_name	last_name	department_name	project_name	hours_allocated
Jack	Anderson	Human Resources	CRM System	120
Jack	Anderson	Human Resources	Mobile App	160
David	Brown	Finance	API Development	100
Leo	Jackson	Sales	NULL	NULL
Quinn	Martinez	Marketing	NULL	NULL
…	…	…	…	…

Employees without projects show NULL (Leo, Quinn have no assignments).

Aggregation Across Multiple Tables

Count projects per department:

SELECT 
    d.department_name,
    COUNT(DISTINCT ep.project_id) AS unique_projects,
    COUNT(ep.employee_id) AS total_assignments,
    SUM(ep.hours_allocated) AS total_hours
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
LEFT JOIN employee_projects ep ON e.employee_id = ep.employee_id
GROUP BY d.department_id, d.department_name
ORDER BY unique_projects DESC;

Expected output:

department_name	unique_projects	total_assignments	total_hours
Engineering	5	7	780
Finance	4	5	540
Human Resources	3	4	480
Sales	2	3	310
Marketing	0	0	NULL
…	…	…	…

Departmental workload analysis.

Key: COUNT(DISTINCT ep.project_id) avoids counting the same project multiple times when multiple employees from same department work on it.

Complex Filtering

Find high-earning engineers on active projects with more than 2 team members:

SELECT 
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name,
    p.project_name,
    p.status AS project_status,
    (SELECT COUNT(*) 
     FROM employee_projects ep2 
     WHERE ep2.project_id = p.project_id) AS team_size
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id
WHERE d.department_name = 'Engineering'
  AND e.salary >= 80000
  AND p.status = 'In Progress'
  AND (SELECT COUNT(*) 
       FROM employee_projects ep2 
       WHERE ep2.project_id = p.project_id) > 2
ORDER BY p.project_name;

Expected output:

first_name	last_name	salary	department_name	project_name	project_status	team_size
Sam	Clark	95000.00	Engineering	Website Redesign	In Progress	4
Paul	Garcia	87000.00	Engineering	Data Pipeline	In Progress	3

High-value engineers on collaborative, active projects.

Pause and Predict: What happens to result size when you add more INNER JOINs?

Answer

The result can **grow exponentially** (Cartesian explosion)! If Employee A is in 3 projects and each project has 4 tasks, joining to tasks creates 3 × 4 = 12 rows for that one employee. This is called "data explosion" and can make queries very slow. Use `DISTINCT` or careful aggregation to manage this.

---

Watch Out — Common Mistakes

Mistake #1: Wrong Join Order Causing Confusion

--  CONFUSING (employees -> projects -> departments)
SELECT ...
FROM employees e
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN departments d ON e.department_id = d.department_id
JOIN projects p ON ep.project_id = p.project_id;

Not wrong technically, but hard to follow.

Better:

-- • CLEARER (employees -> departments, then -> projects)
SELECT ...
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id;

Logical flow: Start with main entity (employees), add direct relationships (departments), then associations (projects).

Mistake #2: Forgetting LEFT JOIN for Optional Data

--  INCOMPLETE (misses employees without projects)
SELECT e.first_name, p.project_name
FROM employees e
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id;

Fix: Use LEFT JOIN for optional relationships:

-- • COMPLETE (includes employees without projects)
SELECT e.first_name, p.project_name
FROM employees e
LEFT JOIN employee_projects ep ON e.employee_id = ep.employee_id
LEFT JOIN projects p ON ep.project_id = p.project_id;

Mistake #3: Cartesian Explosion Without GROUP BY

--  DATA EXPLOSION
SELECT 
    d.department_name,
    COUNT(*) AS count  -- Wrong! This counts ROWS, not unique entities
FROM employees e
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

Problem: If an employee has 3 projects, they’re counted 3 times!

Fix: Use DISTINCT:

-- • CORRECT
SELECT 
    d.department_name,
    COUNT(DISTINCT e.employee_id) AS unique_employees
FROM employees e
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

---

Edge Case Spotlight

Joining Through Many-to-Many

-- Show departments and projects they're involved in (through employees)
SELECT DISTINCT
    d.department_name,
    p.project_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id
ORDER BY d.department_name, p.project_name;

Expected output:

department_name	project_name
Engineering	Data Pipeline
Engineering	Website Redesign
Finance	API Development
Finance	CRM System
…	…

Department-project relationships (even though they’re not directly connected in the schema).

NULL Propagation in Chains

-- LEFT JOIN chain
SELECT 
    e.first_name,
    ep.role,
    p.project_name
FROM employees e
LEFT JOIN employee_projects ep ON e.employee_id = ep.employee_id
LEFT JOIN projects p ON ep.project_id = p.project_id;

Important: Once a LEFT JOIN produces NULL, subsequent LEFT JOINs also produce NULL (NULL doesn’t match anything).

---

Try This

Exercise 1 (Guided)

Create a comprehensive employee report: first_name, last_name, department_name, department location, number of projects they’re assigned to (even if 0). Sort by last_name.

Hint

Start with employees LEFT JOIN to all other tables, use COUNT with GROUP BY, remember to count project_id not *.

Exercise 2 (Independent)

Find departments that have employees working on projects with status ‘Completed’. Show department_name, number of unique completed projects, total hours spent. Sort by total hours descending.

Hint

Join departments -> employees -> employee_projects -> projects, filter WHERE status = 'Completed', GROUP BY department, use COUNT(DISTINCT) and SUM.

Exercise 3 (Challenge)

Create a “project collaboration matrix”: For each project, show project_name, total team size, number of different departments involved, and list the department names (comma-separated). Only include active projects with 3+ team members.

Hint

Multiple joins to projects -> employee_projects -> employees -> departments. Use COUNT(DISTINCT), GROUP_CONCAT for comma-separated list, HAVING for filtering grouped results.

---

Answer Key

Exercise 1 Answer

```sql SELECT e.first_name, e.last_name, d.department_name, d.location, COUNT(ep.project_id) AS project_count FROM employees e JOIN departments d ON e.department_id = d.department_id LEFT JOIN employee_projects ep ON e.employee_id = ep.employee_id GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name, d.location ORDER BY e.last_name; ``` **Expected output:** | first_name | last_name | department_name | location | project_count | |------------|-----------|-----------------|----------|---------------| | Jack | Anderson | Human Resources | Boston | 2 | | David | Brown | Finance | Chicago | 1 | | Sam | Clark | Engineering | San Francisco | 1 | | Leo | Jackson | Sales | Seattle | 0 | | ... | ... | ... | ... | ... | Complete employee overview with project involvement.

Exercise 2 Answer

```sql SELECT d.department_name, COUNT(DISTINCT p.project_id) AS completed_projects, SUM(ep.hours_allocated) AS total_hours FROM departments d JOIN employees e ON d.department_id = e.department_id JOIN employee_projects ep ON e.employee_id = ep.employee_id JOIN projects p ON ep.project_id = p.project_id WHERE p.status = 'Completed' GROUP BY d.department_id, d.department_name ORDER BY total_hours DESC; ``` **Expected output:** | department_name | completed_projects | total_hours | |-----------------|-------------------|-------------| | Engineering | 2 | 420 | | Finance | 1 | 220 | | Sales | 1 | 110 | Departmental productivity on completed work.

Exercise 3 Answer

```sql SELECT p.project_name, COUNT(DISTINCT ep.employee_id) AS team_size, COUNT(DISTINCT d.department_id) AS departments_involved, GROUP_CONCAT(DISTINCT d.department_name ORDER BY d.department_name) AS department_list FROM projects p JOIN employee_projects ep ON p.project_id = ep.project_id JOIN employees e ON ep.employee_id = e.employee_id JOIN departments d ON e.department_id = d.department_id WHERE p.status = 'In Progress' GROUP BY p.project_id, p.project_name HAVING COUNT(DISTINCT ep.employee_id) >= 3 ORDER BY team_size DESC; ``` **Expected output:** | project_name | team_size | departments_involved | department_list | |--------------|-----------|----------------------|-----------------| | Website Redesign | 4 | 3 | Engineering,Finance,Marketing | | Data Pipeline | 3 | 2 | Engineering,Sales | | Mobile App | 3 | 2 | Human Resources,Sales | Cross-functional project analysis. **Note:** GROUP_CONCAT creates comma-separated lists (MySQL-specific function).

---

Quick Recap

• Multiple joins connect 3+ tables in one query
• Chain joins with multiple JOIN…ON clauses
• Mix INNER/LEFT/RIGHT joins based on requirements
• Join order matters for readability
• Watch for Cartesian explosion with many-to-many relationships
• Use COUNT(DISTINCT) to avoid overcounting
• LEFT JOIN chains propagate NULLs
• Start with main entity, add relationships logically

---

Up Next

Time for a Challenge! → Mini Challenge 8

You’ve mastered CASE WHEN, self joins, and multiple joins! Practice these intermediate techniques.