Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 26 of 49

Joining three or more tables

Chain several joins to follow links across employees, assignments, projects and departments, mix inner and left joins safely, and avoid inflated totals.

17 minCombining tablesUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonBuild a query that joins three or four tables, keep unmatched rows in a chain of joins, and avoid counting the same row more than once.

After this lesson you can write queries that reach across three or four tables, keep rows that have no link, and spot the most common way a multi-table query inflates its own numbers.

One more JOIN per table

A join returns a table of rows, and you can join that result to another table, and so on. Each extra table adds one more JOIN ... ON. The ON clause can refer to any table already in the query.

The sample database links people to projects through assignments. To list who works on what, you need three tables: employees, assignments and projects:

sql
SELECT e.first_name, p.name AS project, a.hours
FROM employees e
INNER JOIN assignments a ON a.employee_id = e.id
INNER JOIN projects p ON a.project_id = p.id
WHERE e.id IN (4, 19)
ORDER BY e.id, p.name;
result
+------------+-------------------+-------+
| first_name |      project      | hours |
+------------+-------------------+-------+
| Rohan      | Billing Migration |    40 |
| Rohan      | Customer Portal   |   410 |
| Diya       | Mobile App        |   240 |
+------------+-------------------+-------+

Follow the chain: employee to assignment through employee_id, assignment to project through project_id. The linking table appears in the middle, because it holds both foreign keys. You never select anything from it except hours, but you cannot skip it.

Four tables

Add the department that owns each project and the picture grows by one more join:

sql
SELECT e.first_name, p.name AS project, d.name AS owning_department, a.hours
FROM employees e
INNER JOIN assignments a ON a.employee_id = e.id
INNER JOIN projects p ON a.project_id = p.id
INNER JOIN departments d ON p.department_id = d.id
WHERE e.id = 4
ORDER BY p.name;
result
+------------+-------------------+-------------------+-------+
| first_name |      project      | owning_department | hours |
+------------+-------------------+-------------------+-------+
| Rohan      | Billing Migration | Finance           |    40 |
| Rohan      | Customer Portal   | Engineering       |   410 |
+------------+-------------------+-------------------+-------+

Rohan is in Engineering, yet his second project, Billing Migration, belongs to Finance. The query reads that fact straight out of the data. To build a long query safely, grow it one join at a time and run it after each step, checking the row count.

Writing a long query well is mostly about layout. Put each JOIN on its own line, use consistent aliases, and keep the ON condition immediately after the table it belongs to.

Summarising across the chain

Joined rows can be grouped like any others. Total hours per employee, with names, for the Engineering department:

sql
SELECT e.first_name, e.last_name, COUNT(*) AS projects, SUM(a.hours) AS total_hours
FROM employees e
INNER JOIN assignments a ON a.employee_id = e.id
WHERE e.department_id = 1
GROUP BY e.id, e.first_name, e.last_name
ORDER BY total_hours DESC;
result
+------------+-----------+----------+-------------+
| first_name | last_name | projects | total_hours |
+------------+-----------+----------+-------------+
| Kavya      | Nair      |        2 |         640 |
| Arjun      | Rao       |        2 |         520 |
| Rohan      | Das       |        2 |         450 |
| Isha       | Kapoor    |        2 |         450 |
| Diya       | Chopra    |        1 |         240 |
+------------+-----------+----------+-------------+

Meera does not appear, because she has no assignments and this is an inner join.

Keeping unmatched rows through a chain

To list every employee, including those with no projects, switch to left joins. The rule is to use LEFT JOIN for every step after the first outer one:

sql
SELECT e.id, e.first_name, p.name AS project
FROM employees e
LEFT JOIN assignments a ON a.employee_id = e.id
LEFT JOIN projects p ON a.project_id = p.id
WHERE e.id IN (1, 2, 17, 18)
ORDER BY e.id, p.name;
result
+----+------------+-----------------+
| id | first_name |     project     |
+----+------------+-----------------+
|  1 | Meera      | NULL            |
|  2 | Arjun      | Customer Portal |
|  2 | Arjun      | Mobile App      |
| 17 | Sana       | NULL            |
| 18 | Ravi       | NULL            |
+----+------------+-----------------+

Meera, Sana and Ravi show with a NULL project. Now watch what happens if the last step is an inner join:

sql
SELECT e.id, e.first_name, p.name AS project
FROM employees e
LEFT JOIN assignments a ON a.employee_id = e.id
INNER JOIN projects p ON a.project_id = p.id
WHERE e.id IN (1, 2, 17, 18)
ORDER BY e.id, p.name;
result
+----+------------+-----------------+
| id | first_name |     project     |
+----+------------+-----------------+
|  2 | Arjun      | Customer Portal |
|  2 | Arjun      | Mobile App      |
+----+------------+-----------------+

Only Arjun is left. For the others a.project_id is NULL, the inner join's condition is not true, and the rows disappear. A single inner join later in the chain cancels the effect of the earlier left join.

When joins multiply rows

This is the most common mistake with several tables. A department has employees (many) and projects (many). If you join both to the department in one query, each employee is paired with each project of that department:

sql
SELECT d.name AS department, COUNT(*) AS wrong_count
FROM departments d
INNER JOIN employees e ON e.department_id = d.id
INNER JOIN projects p ON p.department_id = d.id
GROUP BY d.name
ORDER BY d.name;
result
+-------------+-------------+
| department  | wrong_count |
+-------------+-------------+
| Engineering |          12 |
| Finance     |           2 |
| Marketing   |           3 |
| Sales       |           5 |
+-------------+-------------+

Engineering has six employees and two projects, so the count shows 12 rows, which is neither the number of employees nor of projects. Two ways out:

sql
SELECT d.name AS department,
       COUNT(DISTINCT e.id) AS employees,
       COUNT(DISTINCT p.id) AS projects
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
LEFT JOIN projects p ON p.department_id = d.id
GROUP BY d.name
ORDER BY d.name;
result
+-------------+-----------+----------+
| department  | employees | projects |
+-------------+-----------+----------+
| Engineering |         6 |        2 |
| Finance     |         2 |        1 |
| Marketing   |         3 |        1 |
| Research    |         0 |        0 |
| Sales       |         5 |        1 |
| Support     |         3 |        0 |
+-------------+-----------+----------+

COUNT(DISTINCT ...) counts each id once, no matter how often it was repeated. It is simple, and it is fine for counts. It does not repair sums, though. A SUM over multiplied rows is still wrong. The more reliable fix is to summarise each many-side separately in a subquery and join the small results:

sql
SELECT d.name AS department, emp.employees, COALESCE(proj.total_budget, 0) AS total_budget
FROM departments d
LEFT JOIN (SELECT department_id, COUNT(*) AS employees FROM employees GROUP BY department_id) emp
       ON emp.department_id = d.id
LEFT JOIN (SELECT department_id, SUM(budget) AS total_budget FROM projects GROUP BY department_id) proj
       ON proj.department_id = d.id
ORDER BY d.name;
result
+-------------+-----------+--------------+
| department  | employees | total_budget |
+-------------+-----------+--------------+
| Engineering |         6 |      1050000 |
| Finance     |         2 |       300000 |
| Marketing   |         3 |       120000 |
| Research    | NULL      |            0 |
| Sales       |         5 |       200000 |
| Support     |         3 |            0 |
+-------------+-----------+--------------+

Each subquery produces one row per department, so nothing multiplies. The derived tables, emp and proj, are covered properly in the subqueries lesson. Notice that Research, which has no employees, shows NULL for the count. Wrap it in COALESCE if you want 0.

Differences between databases

Chained joins are the same in all four databases. One small difference: MySQL and SQL Server require every subquery in FROM to have an alias (PostgreSQL did too until version 16), so always give derived tables a name as shown above. For MySQL's version of multi-table joins, see INNER JOIN in MySQL.

Common mistakes

  • Missing a join condition. Every added table needs its own ON, or you create an accidental cross join.
  • Switching back to INNER JOIN mid-chain. It removes the unmatched rows an earlier LEFT JOIN kept.
  • Counting after joining two many-sides. Rows multiply. Count distinct ids or summarise each side first.
  • Summing a parent column. A department budget summed after a join to employees is counted once per employee.
  • Writing the whole query at once. Build it one join at a time and check the row counts.

Exercise

For every project, show the project name, the owning department's name, the number of people assigned and the total hours worked. Then list the three employees with the most total hours across all projects, with their department names.

Show one possible answer
sql
SELECT p.name AS project, d.name AS department, COUNT(a.employee_id) AS people, SUM(a.hours) AS total_hours
FROM projects p
INNER JOIN departments d ON p.department_id = d.id
LEFT JOIN assignments a ON a.project_id = p.id
GROUP BY p.id, p.name, d.name
ORDER BY p.name;

SELECT e.first_name, e.last_name, d.name AS department, SUM(a.hours) AS total_hours
FROM employees e
INNER JOIN assignments a ON a.employee_id = e.id
INNER JOIN departments d ON e.department_id = d.id
GROUP BY e.id, e.first_name, e.last_name, d.name
ORDER BY total_hours DESC, e.id
LIMIT 3;
result
+-------------------+-------------+--------+-------------+
|      project      | department  | people | total_hours |
+-------------------+-------------+--------+-------------+
| Billing Migration | Finance     |      3 |         390 |
| Customer Portal   | Engineering |      4 |        1160 |
| Mobile App        | Engineering |      4 |        1100 |
| Q3 Sales Push     | Sales       |      5 |        1050 |
| Spring Campaign   | Marketing   |      3 |         480 |
+-------------------+-------------+--------+-------------+
+------------+-----------+-------------+-------------+
| first_name | last_name | department  | total_hours |
+------------+-----------+-------------+-------------+
| Kavya      | Nair      | Engineering |         640 |
| Arjun      | Rao       | Engineering |         520 |
| Rohan      | Das       | Engineering |         450 |
+------------+-----------+-------------+-------------+

LIMIT 3 works in MySQL, PostgreSQL and SQLite. SQL Server writes SELECT TOP 3 instead, and PostgreSQL also accepts the standard FETCH FIRST 3 ROWS ONLY. The lesson on LIMIT and paging covers the differences.

Key points

  • Each extra table is one more JOIN ... ON. The ON clause links the new table to any table already in the query.
  • Many-to-many data is reached through the linking table: employees to assignments to projects.
  • When mixing join types, an INNER JOIN after a LEFT JOIN can silently drop the NULL rows you wanted to keep.
  • Joining two separate many-sides to one parent multiplies rows. Use COUNT(DISTINCT ...) or summarise each side first.

Check yourself

1. Employees and projects have no direct foreign key. How do you join them?
2. What happens if you INNER JOIN projects after LEFT JOINing assignments from employees?
3. Why can COUNT(*) be too high when a department is joined to both its employees and its projects?
4. Does the order of joins change an INNER JOIN result?