Why joins are the heart of analyst SQL
Business data is stored in separate tables: customers in one, orders in another, products in a third. Almost every real question, such as "revenue by city last month", needs columns from more than one of them.
Joins are how you bring them together. They are also where most wrong numbers in reports come from, so understanding exactly what each join keeps and drops is one of the most valuable SQL skills an analyst can have.
The six joins, with a simple example
Imagine a customers table and an orders table linked by customer_id.
INNER JOIN
Only customers who have placed orders, with their orders. Customers without orders and orders without a known customer are left out.
LEFT JOIN
Every customer, with their orders where they exist. Customers who never ordered appear once, with NULL in the order columns.
RIGHT JOIN
Every order, with customer details where they exist. Rarely needed, since you can swap the tables and use a LEFT JOIN.
FULL OUTER JOIN
Every customer and every order, matched where possible. Useful for finding records that exist on only one side.
CROSS JOIN
Every row of one table paired with every row of the other, for example every store with every month to build a calendar.
SELF JOIN
A table joined to itself, such as employees joined to their own managers in the same employees table.
How to write a join you can trust
A short routine that prevents most join mistakes.
Know the grain of each table
Decide what one row means in each table, such as one customer or one order line, before you join anything.
Check the key is unique where it should be
Run a quick COUNT(*) against COUNT(DISTINCT customer_id). If the "one" side has duplicates, the join will multiply rows.
Pick the join from the question
"Customers who bought" suggests INNER. "All customers, including those who did not buy" needs LEFT.
Count rows before and after
Compare the row count after the join with what you expected. A sudden jump is the clearest sign of duplicate keys.
Filter in the right place
With a LEFT JOIN, a condition on the right table in WHERE removes the unmatched rows. Put it in the ON clause if you want to keep them.
Join mistakes that give wrong totals
These slips produce numbers that look reasonable but are wrong:
- Joining on a key that is not unique, which repeats rows and inflates sums.
- Using INNER JOIN when the question needs everyone, silently dropping customers with no activity.
- Filtering a LEFT JOIN's right table in WHERE, which quietly turns it into an INNER JOIN.
- Forgetting that NULL never equals NULL, so rows with missing keys never match.
- Summing a column from the "one" side after joining to the "many" side, counting the same value several times.
Quick answers about SQL joins
Short answers to the questions analysts search for most.
What is the difference between INNER JOIN and LEFT JOIN?
An INNER JOIN returns only rows with a match in both tables. A LEFT JOIN returns every row from the left table and fills the right table's columns with NULL where there is no match.
Which SQL join is used most by data analysts?
LEFT JOIN and INNER JOIN cover most day-to-day work. LEFT JOIN is especially common because reports usually need to keep every customer, product or date, even with no activity.
Why does my join return more rows than expected?
Usually because the joining column has duplicate values on one or both sides, so each match is repeated. Check uniqueness of the key before joining.
Does MySQL support FULL OUTER JOIN?
No. MySQL does not support FULL OUTER JOIN directly. You can get the same result by combining a LEFT JOIN and a RIGHT JOIN with UNION.
Are SQL joins asked in data analyst interviews?
Yes, very often. Expect to explain the join types and write a query that combines two or three tables, often with GROUP BY.
Go further with SQL for analysis
See where SQL sits in the syllabus, or read the guides and roles that build on it.
Practise joins on two small tables
Make a customers table and an orders table with a few deliberate gaps and duplicates, then run every join type and count the rows each time. Once you can predict the count before running the query, joins stop being risky.

