Joining Multiple Tables in SQL

Real queries frequently join more than two tables. The rule stays the same — you just chain more JOIN ... ON clauses, one relationship at a time.

Sample Tables

employees

departments

locations

Syntax — Chaining Joins

SELECT columns
FROM table1
JOIN table2 ON table1.col = table2.col
JOIN table3 ON table2.col = table3.col;

Example

SELECT e.name, d.department_name, l.city
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN locations l ON d.location_id = l.id;

Expected Output:

Each join adds one more table to the chain — employees → departments → locations — following the foreign key path.

Mixing Join Types in One Query

SELECT e.name, d.department_name, l.city
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN locations l ON d.location_id = l.id;

Using LEFT JOIN throughout ensures employees with no department (or a department with no location) still appear, with NULLs filling the gaps — a common pattern when building a complete report rather than a strict-match one.

Practical Use Case

Almost any real dashboard query: orders joined to customers joined to addresses joined to regions — a handful of joins chained together to assemble one flat, readable result from several normalized tables.

Common Mistakes

• Losing track of which alias refers to which table as the query grows — use short, consistent, meaningful aliases (e for employees, d for departments) rather than single letters reused arbitrarily
• Mixing INNER and LEFT JOINs carelessly in a long chain — an INNER JOIN anywhere in the chain can silently drop rows that a later LEFT JOIN was trying to preserve
• Not verifying the row count after each additional join — one badly-matched join can multiply rows unexpectedly (a hidden one-to-many relationship)

Interview Relevance

Multi-table joins are extremely common in take-home or live SQL exercises using realistic schemas (e-commerce, HR, banking) — practicing with 3–4 table joins, not just two-table toy examples, is what actually prepares you.

Practice Question

Given orders, customers, and cities tables, write a query that lists each order's ID, the customer's name, and their city — using appropriate join types so orders from customers with no city on file are still included.

Related SQL Notes

• SQL Joins Overview
• LEFT JOIN
• Aliases