What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that match on both sides. LEFT JOIN keeps every row from the left table; where the right side has no match, those columns come back NULL. Any time you say "show me the ones without a match too," you need a LEFT JOIN.

Can I join more than two tables?

Yes. You add one JOIN ... ON clause per table; for example, chain order items to orders and orders to customers. As the table count grows, having indexes on the join columns is what keeps the query from slowing down.

What is the difference between ON and WHERE?

ON says how two tables match; WHERE filters the joined result. In an INNER JOIN the two often behave alike, but in a LEFT JOIN, putting a condition in ON versus WHERE changes the result entirely.

Should I use a JOIN or a subquery?

It depends. If you need to show columns from the other table, a JOIN fits better; if you are only filtering by "is it in this list?", a subquery can read more clearly. We compare the two in a separate guide.

← All postsThe PerSight Team · Last updated: July 23, 2026 · 7 min read

SQL JOINs: Combining Two Tables the Right Way

A practical guide to joining tables like orders and customers in SQL: the difference between INNER JOIN and LEFT JOIN, the row-multiplication trap, and real examples that run on all four databases.

Most of your data lives in more than one place. Orders sit in one table; the name and city of the customer who placed them sit in another. Ask "how much did customers in London spend last month?" and the answer lives at the intersection of the two. The SQL command that brings tables together is JOIN, and it is far simpler than its reputation suggests.

Why are the tables split up?

Instead of retyping a customer's name on every order, you store the customer once in a customers table and keep only a customer_id on the order. This is called normalization; it saves space and lets you fix a name in exactly one place. The cost is that when you build a report, you have to stitch the tables back together.

How do you write an INNER JOIN?

This is the join you will reach for most often. It returns the rows that match on both sides. Let's connect customers and orders on customer_id:

SELECT c.name, o.amount, o.order_date
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;

Identical syntax on PostgreSQL, SQL Server, MySQL, and Oracle

The ON line is the matching rule: which column lines up with which. The o and c aliases keep the query short. An INNER JOIN returns only rows with a counterpart on both sides; an order whose customer was deleted never shows up.

What if I also want customers with no orders?

This is the classic case an INNER JOIN cannot answer, because there is no match to return. A LEFT JOIN keeps every row from the left table (here customers) and fills the right-hand columns with NULL when nothing matches:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

The WHERE o.id IS NULL filter keeps only the customers with no match on the right, the ones who never ordered. It is one of the most useful patterns LEFT JOIN gives you.

The trap nobody warns you about: multiplied rows

If a customer has five orders, the join produces five rows for that customer. Count rows without noticing, or treat the customer as a single row and average something, and your numbers inflate. The rule of thumb: after a join you almost always group and aggregate.

SELECT c.name,
       COUNT(o.id) AS order_count,
       SUM(o.amount) total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY total DESC;

We cover that GROUP BY and aggregation step in its own guide; it is the step that follows a join almost every time.

Does it differ by database?

Good news: INNER JOIN and LEFT JOIN are written the same way on PostgreSQL, SQL Server, MySQL, and Oracle. The one thing to watch for is old Oracle code that writes outer joins with the (+) operator; prefer the standard LEFT JOIN in new queries. FULL OUTER JOIN does not exist in MySQL at all; you emulate it by UNION-ing two LEFT JOINs.

Frequently asked questions

What is the difference between INNER JOIN and LEFT JOIN?: INNER JOIN returns only rows that match on both sides. LEFT JOIN keeps every row from the left table; where the right side has no match, those columns come back NULL. Any time you say "show me the ones without a match too," you need a LEFT JOIN.
Can I join more than two tables?: Yes. You add one JOIN ... ON clause per table; for example, chain order items to orders and orders to customers. As the table count grows, having indexes on the join columns is what keeps the query from slowing down.
What is the difference between ON and WHERE?: ON says how two tables match; WHERE filters the joined result. In an INNER JOIN the two often behave alike, but in a LEFT JOIN, putting a condition in ON versus WHERE changes the result entirely.
Should I use a JOIN or a subquery?: It depends. If you need to show columns from the other table, a JOIN fits better; if you are only filtering by "is it in this list?", a subquery can read more clearly. We compare the two in a separate guide.

Rather than hand-write this join for every report, you can type it into PerSight in plain English. Ask for "total order amount per customer, biggest first" and review the SQL it produces before you run it.