A join combines rows from two tables using a column they share. An INNER JOIN keeps only the rows that match in both tables. A LEFT JOIN keeps every row from the first table and fills in blanks where the second has no match. Almost every real analytics question needs at least one.
Joins are also where beginners lose data without noticing, because a query that silently drops rows still returns a result and still looks correct. This article covers the six join types, when each is right, and the three mistakes that produce wrong numbers.
The two tables used throughout
Every example below uses the same pair of tables from a Bengaluru electronics retailer, so you can follow what changes.
The customers table holds four people:
| customer_id | customer_name | city |
|---|---|---|
| 1 | Ananya Rao | Bengaluru |
| 2 | Vikram Nair | Chennai |
| 3 | Priya Menon | Kochi |
| 4 | Rohit Shah | Pune |
The orders table holds four orders, and one of them belongs to a customer who is not in the table above:
| order_id | customer_id | amount |
|---|---|---|
| 101 | 1 | 24999.00 |
| 102 | 1 | 8499.00 |
| 103 | 2 | 51200.00 |
| 104 | 7 | 3200.00 |
Note the mismatches deliberately. Priya and Rohit have placed no orders. Order 104 belongs to customer 7, who does not exist in customers. Those two facts are what make the different joins behave differently.
The six join types
| Join | What it returns | Syntax |
|---|---|---|
| INNER JOIN | Only rows matching in both tables | SELECT … FROM a INNER JOIN b ON a.id = b.id |
| LEFT JOIN | All rows from the left table, matched rows from the right | SELECT … FROM a LEFT JOIN b ON a.id = b.id |
| RIGHT JOIN | All rows from the right table, matched rows from the left | SELECT … FROM a RIGHT JOIN b ON a.id = b.id |
| FULL OUTER JOIN | All rows from both, matched where possible | SELECT … FROM a FULL OUTER JOIN b ON a.id = b.id |
| CROSS JOIN | Every row of a paired with every row of b | SELECT … FROM a CROSS JOIN b |
| SELF JOIN | A table joined to itself under two aliases | SELECT … FROM a x JOIN a y ON x.mgr = y.id |
INNER JOIN: only what matches
INNER JOIN returns rows only where the join condition is satisfied in both tables. It is the default assumption most people have when they say “join”.
Query: SELECT c.customer_name, o.amount FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
Result: three rows. Ananya twice, for her two orders, and Vikram once. Priya and Rohit vanish because they have no orders. Order 104 vanishes because customer 7 does not exist.
| customer_name | amount |
|---|---|
| Ananya Rao | 24999.00 |
| Ananya Rao | 8499.00 |
| Vikram Nair | 51200.00 |
This is the join that quietly loses data. If your question was “what did each customer spend”, this answer omits two of your four customers entirely, and a total built on it understates your customer count by half.
LEFT JOIN: keep everything on the left
LEFT JOIN returns every row from the first table, whether or not it matched, filling the right-hand columns with NULL where there was no match.
Query: SELECT c.customer_name, o.amount FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id
| customer_name | amount |
|---|---|
| Ananya Rao | 24999.00 |
| Ananya Rao | 8499.00 |
| Vikram Nair | 51200.00 |
| Priya Menon | NULL |
| Rohit Shah | NULL |
Now all four customers appear. Priya and Rohit show NULL amounts, which is the correct representation of “this customer exists and has spent nothing”. Order 104 still does not appear, because it has no matching customer and customers is the left table.
LEFT JOIN is the one to reach for by default in analytics. Most business questions are of the form “all of X, with Y where it exists”, and that is exactly a LEFT JOIN.
RIGHT JOIN: the mirror image
RIGHT JOIN keeps every row from the second table instead. On our data it surfaces the orphan order.
Query: SELECT c.customer_name, o.order_id FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id
| customer_name | order_id |
|---|---|
| Ananya Rao | 101 |
| Ananya Rao | 102 |
| Vikram Nair | 103 |
| NULL | 104 |
Order 104 appears with a NULL customer name, which is how you find data-quality problems: orders attached to customers who were deleted or never created.
In practice RIGHT JOIN is rare. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, and teams generally standardise on LEFT for readability. Know it for interviews; reach for LEFT at work.
FULL OUTER JOIN: keep everything
FULL OUTER JOIN returns every row from both tables, matching where it can and padding with NULL where it cannot.
| customer_name | order_id |
|---|---|
| Ananya Rao | 101 |
| Ananya Rao | 102 |
| Vikram Nair | 103 |
| Priya Menon | NULL |
| Rohit Shah | NULL |
| NULL | 104 |
All six possibilities in one result. This is the reconciliation join: use it when you need to find everything that failed to match in either direction, such as comparing a payments file against an orders file.
One dialect warning. MySQL does not support FULL OUTER JOIN directly. You reproduce it with a LEFT JOIN and a RIGHT JOIN combined using UNION. PostgreSQL, SQL Server and Oracle all support it natively.
CROSS JOIN and SELF JOIN
CROSS JOIN pairs every row of one table with every row of the other, with no condition. Four customers crossed with four orders gives sixteen rows. It is occasionally useful for generating combinations, such as every product against every month for a reporting grid. It is much more often an accident: forget the ON clause and some databases give you a cross join instead of an error, which is how a query that should return 400 rows returns 160,000.
SELF JOIN joins a table to itself using two aliases. The standard case is a hierarchy, such as an employees table where each row holds a manager_id pointing at another row in the same table.
Query: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id
The LEFT JOIN matters there: use INNER and anyone without a manager, typically the founder, drops out of your org chart.
Three mistakes that produce wrong numbers
Using INNER when you meant LEFT. The most common and most expensive. Your row count silently drops and every aggregate built on it is understated. If a total looks lower than expected, check your join type first.
Joining on a column that is not unique. If the right-hand table has several rows per key, the left rows multiply. Join orders to order_items and a customer with one order and five items appears five times; sum the order amount now and you have counted it five times. This is called fan-out, and it is the reason revenue reports sometimes come out several times too high.
Filtering an outer join in the WHERE clause. Write a LEFT JOIN and then add WHERE o.amount is greater than 1000, and every NULL row fails that test and disappears, turning your LEFT JOIN back into an INNER JOIN. Put conditions on the right-hand table in the ON clause instead, and keep the WHERE clause for the left-hand table.
That third one catches experienced people. It is worth reading twice.
Frequently asked questions
What is a join in SQL?
A join combines rows from two or more tables based on a related column between them, usually a shared key such as customer_id. It lets you answer questions that span tables, for example pulling customer names from one table alongside their order amounts from another, without duplicating that data in a single table.
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only the rows where the join condition matches in both tables. LEFT JOIN returns every row from the left table regardless of whether it matched, filling the right-hand columns with NULL where there is no match. Use INNER when you only care about matched records, and LEFT when you need the complete left-hand set.
Which SQL join is used most often?
LEFT JOIN, in analytics work. Most business questions take the shape “show me all customers, with their orders where they have any”, which requires keeping unmatched left-hand rows. INNER JOIN is common too, but it silently removes rows, so it should be a deliberate choice rather than a default.
Does MySQL support FULL OUTER JOIN?
No. MySQL has no native FULL OUTER JOIN. The standard workaround is to write a LEFT JOIN and a RIGHT JOIN over the same tables and combine them with UNION, which removes the duplicated matched rows. PostgreSQL, Microsoft SQL Server and Oracle all support FULL OUTER JOIN directly.
Why does my join return more rows than the original table?
Almost always because the column you joined on is not unique in the second table. If one customer has five order items, joining customers to order_items produces five rows for that customer. This is called fan-out. Check for duplicates on the join key, and aggregate the second table before joining if you only need one row per key.
Can you join more than two tables in one query?
Yes. You chain joins, each with its own ON clause, and there is no practical limit beyond query performance and readability. A typical analytics query joins three to six tables. Join order and the presence of indexes on the join columns matter a great deal for speed once tables get large.
Get past the syntax and into real queries
Joins are easy to read about and hard to get right under pressure, because the failures are silent. Nobody learns fan-out from an article; they learn it from a report that came out three times too high and a mentor who showed them why.
SkilloVilla’s SQL: Beginner to Advanced course at ₹33,110 runs live, with mentors reviewing the queries you write rather than just marking them right or wrong. For SQL as part of a full analytics path with Python and statistics, the Data Analytics with Python track is ₹71,999, currently ₹58,999 and carries placement support.
Fees and ratings last checked August 2026; confirm current numbers with the provider before enrolling.
