Almost every useful question you’ll ask of a database needs data from more than one table. Customers live in one place, orders in another, products in a third. Joins are how you put them back together.
They’re also where most beginners get quietly wrong answers. Your query runs, it returns rows, and the numbers look plausible. Nobody tells you that you dropped 200 customers because you picked the wrong join, or that your revenue total is double because a join multiplied rows behind your back.
This guide covers the four joins you’ll actually use, what each one does to your row count, and the two mistakes that produce wrong results without producing an error.
Table of contents
- What a join actually does
- The four joins, side by side
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN and FULL OUTER JOIN
- What each join does to your row count
- Two mistakes that give you wrong answers
- How to practice joins
- Turn SQL into a data career
- FAQs about SQL joins
What a join actually does
A join matches rows from one table to rows in another using a condition you write, then returns the combined columns as a single result.
Say you have a customers table with customer_id and name, and an orders table with order_id, customer_id, and amount. The customer_id column appears in both. That shared column is what lets you connect them.
The basic shape looks like this:
SELECT c.name, o.amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id
The ON clause is the matching rule. Everything interesting about joins comes down to one question: what happens to rows that don’t find a match? Each join type answers that differently, and that answer is the whole lesson.
The four joins, side by side
Think of your two tables as two overlapping circles. The overlap is the rows that match on both sides. Each join keeps a different part of that picture.
“Left” and “right” are literal. The left table is whatever you wrote after FROM. The right table is whatever you wrote after JOIN. That’s it.
INNER JOIN
An INNER JOIN returns only rows where the match succeeded on both sides. No match, no row. It’s the default when you write JOIN without a qualifier.
SELECT c.name, o.amount FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
Customers who never ordered don’t appear. Orders whose customer was deleted don’t appear either. You get the clean intersection.
Use it when you genuinely only care about complete pairs. “Show me every order with the customer’s name attached” is an inner join question. The risk is that it silently hides things. If 20 percent of your customers never placed an order, an inner join makes them invisible, and your customer count is now wrong.
LEFT JOIN
A LEFT JOIN keeps every row from the left table whether or not it found a match. Where there’s no match, the right table’s columns come back as NULL.
SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id
Now every customer appears. The ones who never ordered show NULL in the amount column. This is the join analysts reach for most, because the usual question is “show me all of X, and whatever Y data exists for them.”
It’s also the tool for finding gaps. Add a filter for rows where the right side came back empty and you get exactly the records with nothing on the other side:
SELECT c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL
That query lists every customer who has never placed an order. It’s one of the most useful patterns in SQL and worth memorizing.
RIGHT JOIN and FULL OUTER JOIN
A RIGHT JOIN is a LEFT JOIN with the tables reversed. It keeps every row from the right table and fills NULLs on the left where nothing matched.
You’ll rarely write one. Most people find it easier to swap the table order and use a LEFT JOIN, because reading a query is simpler when the table you care about is the one you started with. Both produce identical results.
A FULL OUTER JOIN keeps everything from both tables, matching where it can and filling NULLs on either side where it can’t.
SELECT c.name, o.amount FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
This is the reconciliation join. When you’re comparing two systems and need to see records that exist in one but not the other, in both directions at once, this is the one. Worth knowing that MySQL doesn’t support FULL OUTER JOIN directly, so you emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION. PostgreSQL, SQL Server, and Oracle all support it natively, as documented in the PostgreSQL join documentation.
What each join does to your row count
Here’s a small example that makes the difference concrete. Take a customers table with 5 rows and an orders table with 6 rows. Five of those orders belong to customers C1, C2, and C3. Customers C4 and C5 never ordered. One order is an orphan whose customer record was deleted.
The INNER JOIN drops three things you might have wanted. The FULL OUTER keeps everything including the problems. Which is correct depends entirely on the question you’re answering, and that’s a judgment call, not a syntax rule.
A quick reference for picking one:
| Your question | Join to use |
|---|---|
| Only records that exist in both tables | INNER JOIN |
| All records from the main table, plus any related data | LEFT JOIN |
| Records in the main table with nothing on the other side | LEFT JOIN with a NULL filter |
| Everything from both sides, including mismatches | FULL OUTER JOIN |
Two mistakes that give you wrong answers
These don’t throw errors. That’s what makes them dangerous.
Filtering the right table in WHERE instead of ON. If you write a LEFT JOIN and then add a WHERE condition on a column from the right table, you’ve turned it back into an INNER JOIN. The unmatched rows have NULL in that column, NULL fails the WHERE test, and they get thrown out. To filter the right table while keeping your unmatched rows, put the condition in the ON clause instead.
Accidental row multiplication. If the column you’re joining on isn’t unique in the right table, every matching row multiplies. Join a customer to 3 orders and that customer appears 3 times. Sum a customer-level column across that result and you’ve tripled it. This is the single most common cause of inflated revenue numbers in analyst reports.
The habit that catches both: check your row count before and after every join. If the count changed in a way you didn’t expect, stop and find out why before you aggregate anything. Running a COUNT on your join key first tells you whether it’s unique.
How to practice joins
Reading about joins doesn’t build the instinct. Writing a hundred of them does.
Start with free interactive tools where you get instant feedback. SQLBolt walks through joins in the browser with no setup at all. Mode’s SQL tutorial uses real datasets and is closer to the analyst work you’d actually do.
Then build something with your own data. Download any two related CSV files, load them into a free PostgreSQL or SQLite database, and answer real questions with joins. The moment you have to decide which join a question needs, the concept clicks in a way that exercises don’t deliver.
If you’re just getting oriented with databases, our primer on what SQL is and how it works covers the ground beneath this article.
Turn SQL into a data career
Joins show up in essentially every data analyst interview. Not as trivia, but as a live exercise where someone hands you two tables and watches how you think about the matching rule.
SQL is one piece of the stack employers hire for. Coding Temple’s data analytics bootcamp pairs it with Excel, Python, and visualization tools like Power BI and Tableau, taught through projects using messy multi-table datasets. Career services run alongside the coursework, so interview prep happens while you learn rather than after.
Wondering whether the field fits you? Our honest take on whether data analytics is hard is a good reality check, and the free data analytics course lets you try before committing. When you’re ready, apply to Coding Temple.
FAQs about SQL joins
What is the difference between INNER JOIN and LEFT JOIN?
An INNER JOIN returns only rows that matched in both tables. A LEFT JOIN returns every row from the left table regardless of whether it matched, filling the right table’s columns with NULL where there was no match. If every left row has a match, the two return identical results.
Which SQL join is used most often?
INNER JOIN and LEFT JOIN cover the large majority of real queries. RIGHT JOIN is rare because reversing the table order and using a LEFT JOIN reads more clearly. FULL OUTER JOIN shows up mainly in reconciliation work.
Why did my join return more rows than the original table?
The join key isn’t unique in the table you joined to. Each matching row on that side creates another output row, so one customer with four orders becomes four rows. Check whether your join column is unique before you aggregate anything.
Can you join more than two tables in one query?
Yes. Chain additional JOIN clauses and each one connects to the result built so far. Joining five or six tables is routine in reporting queries. Add them one at a time and check your row count after each, because a multiplication problem in the third join is hard to find once six are stacked.
What is the difference between JOIN and UNION?
A join combines columns from two tables side by side, matching rows on a condition. A UNION stacks the rows of two result sets on top of each other and requires the same column structure in both. Joins make your result wider. Unions make it longer.
Does MySQL support FULL OUTER JOIN?
No, MySQL has no native FULL OUTER JOIN. You reproduce it by running a LEFT JOIN and a RIGHT JOIN over the same tables and combining them with UNION. PostgreSQL, SQL Server, and Oracle all support the syntax directly.