On this page · 10 sections
- The pattern: one JOIN per extra table
- Worked example: the schema
- How to join 3 tables with INNER JOIN
- How to join 3 tables with LEFT JOIN
- Two mistakes that silently drop rows
- Aggregate across 3 tables: total spend per customer
- PostgreSQL vs MySQL vs SQL Server: documented differences
- For data teams and businesses
- How eCorpIT can help
- References
Summary. To join 3 tables in SQL, chain two JOIN clauses, each with its own ON condition: FROM customers c JOIN orders o ON o.customer_id = c.customer_id JOIN products p ON p.product_id = o.product_id. Use INNER JOIN for rows that match in all 3 tables, and LEFT JOIN on both joins when every row of the first table must appear. In the worked example below (4 customers, 4 orders, 3 products), the INNER JOIN returns 4 rows and the LEFT JOIN returns 5, because one customer has no orders. Two mistakes cause most wrong results: a LEFT JOIN followed by an INNER JOIN, which drops the unmatched rows again, and filtering the right-hand table in WHERE instead of ON. The syntax is the same in PostgreSQL 18, MySQL 8.4 and 9.7, and SQL Server 2016 or later, with narrow documented differences: MySQL has no FULL OUTER JOIN, SQL Server takes ON but not USING or NATURAL, and MySQL caps one join at 61 tables.
About this article. For developers, analysts and data teams who write reporting queries. On 15 November 2026 we checked every syntax rule and limit against the PostgreSQL 18 documentation, the MySQL 8.4 and 9.7 manuals and Microsoft Learn's Transact-SQL reference, and ran every query on local PostgreSQL 16 and MySQL 9.6 instances; SQL Server results follow from its documented syntax but were not executed. No vendor paid for inclusion. Researched with AI assistance and reviewed by our editorial team.
The pattern: one JOIN per extra table
Every extra table needs its own JOIN and its own join condition. For 3 tables you write 2 joins:
SELECT a.col, b.col, c.col
FROM table_a AS a
INNER JOIN table_b AS b ON b.a_id = a.a_id
INNER JOIN table_c AS c ON c.c_id = b.c_id;
The middle table usually holds the keys that connect the other two; Microsoft notes it can bridge them without any of its columns appearing in the select list. PostgreSQL's documentation adds the rule that matters most for 3 or more tables: without parentheses, JOIN clauses nest from left to right, so the second join works on the result of the first.
Worked example: the schema
This schema ran unchanged on PostgreSQL and MySQL; it uses only plain types, table-level FOREIGN KEY constraints and multi-row INSERTs, all within SQL Server's documented syntax.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
country CHAR(2) NOT NULL
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
price DECIMAL(8,2) NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
FOREIGN KEY (product_id) REFERENCES products (product_id)
);
INSERT INTO customers VALUES (1, 'Ava', 'US'), (2, 'Ben', 'UK'), (3, 'Chloe', 'US'), (4, 'Dan', 'UK');
INSERT INTO products VALUES (10, 'Laptop stand', 45.00), (20, 'USB-C hub', 30.00), (30, 'Webcam', 60.00);
INSERT INTO orders VALUES (101, 1, 10, 2), (102, 1, 20, 1), (103, 2, 30, 1), (104, 3, 20, 3);
The sample data, side by side:
| customers | products | orders (order, customer, product, qty) |
|---|---|---|
| 1 Ava (US) | 10 Laptop stand, 45.00 | 101: 1, 10, 2 |
| 2 Ben (UK) | 20 USB-C hub, 30.00 | 102: 1, 20, 1 |
| 3 Chloe (US) | 30 Webcam, 60.00 | 103: 2, 30, 1 |
| 4 Dan (UK) | 104: 3, 20, 3 |
Dan has no orders. That one row is what separates the INNER and LEFT results.
How to join 3 tables with INNER JOIN
- Start FROM the table whose rows you are reporting on (customers).
- Join the bridging table (orders) on its foreign key to the first table.
- Join the third table (products) on the key stored in the bridging table.
- Alias every table and qualify every column. PostgreSQL's tutorial calls this good style, because the query will not break if a same-named column is later added to another table.
SELECT c.name, o.order_id, p.product_name, o.quantity * p.price AS line_total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id
INNER JOIN products AS p ON p.product_id = o.product_id
ORDER BY o.order_id;
| name | order_id | product_name | line_total |
|---|---|---|---|
| Ava | 101 | Laptop stand | 90.00 |
| Ava | 102 | USB-C hub | 30.00 |
| Ben | 103 | Webcam | 60.00 |
| Chloe | 104 | USB-C hub | 90.00 |
4 rows (2 x 45.00 = 90.00 and 3 x 30.00 = 90.00). Dan is missing because an inner join returns a row only where the join condition finds a match.
How to join 3 tables with LEFT JOIN
To list every customer, including those with no orders, use LEFT JOIN for both joins:
SELECT c.name, o.order_id, p.product_name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
LEFT JOIN products AS p ON p.product_id = o.product_id
ORDER BY c.customer_id, o.order_id;
| name | order_id | product_name |
|---|---|---|
| Ava | 101 | Laptop stand |
| Ava | 102 | USB-C hub |
| Ben | 103 | Webcam |
| Chloe | 104 | USB-C hub |
| Dan | NULL | NULL |
5 rows. A LEFT JOIN returns at least one row for every left-table row and fills the right side with NULL when nothing matches. To list customers who have never ordered, add WHERE o.order_id IS NULL, a pattern the MySQL manual documents for finding rows with no counterpart; here it returns only Dan.
Two mistakes that silently drop rows
Mistake 1: a LEFT JOIN followed by an INNER JOIN
This query looks as if it keeps every customer, but it returns 4 rows:
SELECT c.name, o.order_id, p.product_name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
INNER JOIN products AS p ON p.product_id = o.product_id;
Joins nest from left to right, so the INNER JOIN runs against the output of the LEFT JOIN. Dan's row has NULL in o.product_id; a comparison with NULL is never true, and rows match only when the ON expression is true, so Dan is discarded. Use LEFT JOIN for every later table, or group the inner join in parentheses, which returns all 5 rows:
SELECT c.name, o.order_id, p.product_name
FROM customers AS c
LEFT JOIN (orders AS o INNER JOIN products AS p ON p.product_id = o.product_id)
ON o.customer_id = c.customer_id;
Mistake 2: filtering the right-hand table in WHERE instead of ON
-- Condition in ON: 4 rows (Ava 101, Ben NULL, Chloe 104, Dan NULL)
SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id AND o.quantity >= 2;
-- Condition in WHERE: 2 rows (Ava 101, Chloe 104)
SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.quantity >= 2;
PostgreSQL and SQL Server give the same reason: a condition in ON is applied before the join, WHERE to the joined result. That makes no difference for inner joins but changes outer-join answers. MySQL's optimiser documentation goes further: when a WHERE condition can never be true for the NULL-filled row, MySQL changes the LEFT JOIN into an inner join.
Aggregate across 3 tables: total spend per customer
A join returns one row for each matching pair, so a customer with 2 orders appears twice above. Aggregate with GROUP BY to get one row per customer:
SELECT c.name,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.quantity * p.price), 0.00) AS total_spend
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
LEFT JOIN products AS p ON p.product_id = o.product_id
GROUP BY c.customer_id, c.name
ORDER BY total_spend DESC;
| name | order_count | total_spend |
|---|---|---|
| Ava | 2 | 120.00 |
| Chloe | 1 | 90.00 |
| Ben | 1 | 60.00 |
| Dan | 0 | 0.00 |
Ava's total is 90.00 + 30.00 = 120.00. COUNT of a column counts only non-NULL values, which is why Dan shows 0 rather than 1, and COALESCE turns his NULL sum into 0.00.
PostgreSQL vs MySQL vs SQL Server: documented differences
The table below summarises each vendor's join reference as checked on 15 November 2026.
| Feature | PostgreSQL 18 | MySQL 8.4 and 9.7 | SQL Server 2016 and later, Azure SQL |
|---|---|---|---|
| INNER, LEFT and RIGHT JOIN with ON | Yes | Yes | Yes |
| FULL [OUTER] JOIN | Yes | No | Yes |
| USING (column) | Yes | Yes | No, ON only |
| NATURAL JOIN | Yes, docs call it riskier than USING | Yes | No |
| Plain JOIN means | INNER JOIN | INNER JOIN (also equivalent to CROSS JOIN) | INNER JOIN; INNER is required with a join hint |
| Size limit in docs | None stated on join pages | 61 tables in one join | Up to 256 table sources per statement |
| Comma mixed with JOIN | JOIN binds more tightly than a comma | Same; can raise an unknown column error | Docs recommend join conditions in FROM |
In practice, write explicit JOIN ... ON with aliases and the same query runs on all 3 engines. Avoid mixing comma joins with JOIN, because the lower-precedence comma can hide a table from an ON clause. Prefer LEFT JOIN to RIGHT JOIN: MySQL recommends it for portability and rewrites right joins as left joins anyway.
Which versions this applies to
PostgreSQL 18 is current, at minor version 18.7. PostgreSQL 19 reached release candidate 2 on 12 November 2026 with general availability planned for 19 November, and its joined-table documentation is identical to 18. That 12 November update also fixed planner bugs that could give wrong results for outer-join queries, so stay on the current minor release. Our PostgreSQL 19 testing guide covers the other changes.
MySQL 8.4 and 9.7 are long-term support lines; releases after 9.7, such as 26.7 and 26.10, use year-and-month version numbers. The JOIN clause page matches on every point above in the 8.4, 9.7 and 26.10 manuals. If you are still on MySQL 8.0, see our MySQL 8.0 upgrade plan.
Microsoft's FROM clause reference covers SQL Server 2016 and later, including SQL Server 2025 (17.x), plus Azure SQL Database, Azure SQL Managed Instance and SQL database in Microsoft Fabric.
For data teams and businesses
A wrong join still returns plausible numbers, so a few controls are worth adding:
- Row-count checks. Compare the base table's row count with the count after each join. A drop usually means an INNER JOIN or WHERE filter is removing unmatched rows; a rise means a one-to-many join is duplicating rows.
- Reusable views. Put agreed joins in a view so analysts do not each rewrite them.
- Review rules. Flag any LEFT JOIN followed by an INNER JOIN on the same chain, and any WHERE condition on a LEFT-joined table.
- Migration checks. When moving reports between engines, for example in a SQL Server 2017 exit to SQL Server 2025, Azure SQL Managed Instance or PostgreSQL, search first for USING, NATURAL and FULL OUTER JOIN, the documented portability gaps.
How eCorpIT can help
eCorpIT is a Gurugram-based technology organisation, founded in 2021, assessed at CMMI Level 5 and MSME certified, with senior-led engineering teams working across AWS, Microsoft and Google platforms. Our data and analytics services team designs schemas, reporting views and engine-to-engine migration plans for PostgreSQL, MySQL and SQL Server. Talk to us at /contact-us/.
Last updated: 15 November 2026.