How to join 3 tables in SQL: INNER, LEFT and worked examples

Chain two JOIN clauses, each with its own ON condition: INNER JOIN keeps rows matching in all 3 tables, LEFT JOIN keeps every row of the first.

Read time
12 min
Word count
1.4K
Sections
10
FAQs
8
Share
Two developers link three printed table cards on a wooden desk under the headline Join three tables in SQL
On this page · 10 sections
  1. The pattern: one JOIN per extra table
  2. Worked example: the schema
  3. How to join 3 tables with INNER JOIN
  4. How to join 3 tables with LEFT JOIN
  5. Two mistakes that silently drop rows
  6. Aggregate across 3 tables: total spend per customer
  7. PostgreSQL vs MySQL vs SQL Server: documented differences
  8. For data teams and businesses
  9. How eCorpIT can help
  10. 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

  1. Start FROM the table whose rows you are reporting on (customers).
  1. Join the bridging table (orders) on its foreign key to the first table.
  1. Join the third table (products) on the key stored in the bridging table.
  1. 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.

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.

References

  1. PostgreSQL 18 documentation, 7.2 Table Expressions (Joined Tables) — PostgreSQL Global Development Group
  1. FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL) — Microsoft Learn
  1. MySQL 9.7 Reference Manual, 15.2.13.2 JOIN Clause — Oracle
  1. Joins (SQL Server) — Microsoft Learn
  1. CREATE TABLE (Transact-SQL) — Microsoft Learn
  1. Table Value Constructor (Transact-SQL) — Microsoft Learn
  1. PostgreSQL 18 documentation, 2.6 Joins Between Tables — PostgreSQL Global Development Group
  1. MySQL 9.7 Reference Manual, 10.2.1.9 Outer Join Optimization — Oracle
  1. PostgreSQL 18 documentation, 9.21 Aggregate Functions — PostgreSQL Global Development Group
  1. MySQL 8.4 Reference Manual, 15.2.13.2 JOIN Clause — Oracle
  1. MySQL 8.4 Reference Manual, 10.2.1.10 Outer Join Simplification — Oracle
  1. Versioning policy — PostgreSQL Global Development Group
  1. PostgreSQL 18.7, 17.12, 16.16, 15.20, 14.25, and 19 RC 2 Released, 12 November 2026 — PostgreSQL Global Development Group
  1. PostgreSQL 19 documentation, 7.2 Table Expressions — PostgreSQL Global Development Group
  1. MySQL 26.10 Reference Manual, 1.3 MySQL Releases: Innovation and LTS — Oracle
  1. MySQL 26.10 Reference Manual, 15.2.13.2 JOIN Clause — Oracle
  1. What's new in SQL Server 2025 — Microsoft Learn

Frequently asked

Quick answers.

01 How do you join 3 tables in SQL?
Write one JOIN clause for each table after the first, each with its own ON condition: join customers to orders on customer_id, then orders to products on product_id. Use INNER JOIN to keep only rows matching in all 3 tables, or LEFT JOIN to keep every row of the first.
02 What is the difference between INNER JOIN and LEFT JOIN with three tables?
An INNER JOIN returns only rows that match in every joined table, so a customer with no orders disappears. A LEFT JOIN keeps every row of the left table and fills unmatched columns with NULL. In this guide's example, INNER returns 4 rows and LEFT returns 5.
03 Does the order of joins matter when joining three tables?
For inner joins alone, regrouping does not change the result. With outer joins, order matters, because JOIN clauses nest left to right when there are no parentheses. A LEFT JOIN followed by an INNER JOIN on the right-hand table removes the rows the LEFT JOIN was meant to keep.
04 Can I join three tables without a common column in all of them?
Yes. Each join only needs a condition between the new table and a table already in the query, and a middle table such as orders usually holds keys to both others. Microsoft notes that the bridging table's columns need not appear in the select list.
05 Does MySQL support FULL OUTER JOIN?
Not in its JOIN syntax. The MySQL 8.4 and 9.7 reference manuals list INNER, CROSS, LEFT, RIGHT, NATURAL and STRAIGHT_JOIN forms, but no FULL OUTER JOIN. PostgreSQL and SQL Server both document FULL OUTER JOIN, so check for it before moving a report from either of them to MySQL.
06 Why does my three-table join return duplicate rows?
A join repeats a row once for every matching row in the next table, so a customer with 2 orders appears twice. That is correct behaviour, not a bug. For one row per customer, aggregate with GROUP BY and functions such as COUNT and SUM.
07 Can I use USING instead of ON in SQL Server?
No. SQL Server's documented joined-table syntax requires ON with a search condition and does not include USING or NATURAL JOIN. PostgreSQL and MySQL both support USING with a column list, which also returns the shared column only once. Writing explicit ON conditions keeps a query portable across all 3 databases.
08 How many tables can one SQL query join?
MySQL documents a maximum of 61 tables in a single join, including views and derived tables merged into the query. SQL Server allows up to 256 table sources per statement, though complex queries may hit a lower limit. PostgreSQL's join pages state no fixed maximum.

About the author

Manu Shukla

Founder & Director

Founder of eCorpIT. Hands-on engineer leading senior-only delivery for AI apps, custom software, and cloud systems for global clients.

Subscribe

One engineering note a week. No fluff, no spam.

Senior-architect playbooks on AI agents, mobile apps, cloud, security, data, and marketing — delivered every Wednesday.

Past the reading

Read enough. Let's build something.

A senior architect responds in 24 working hours with scope, indicative cost, and a timeline. NDA before any technical conversation.

Loading search…

Browse all guides