SQL Joins Explained: INNER, LEFT, RIGHT, FULL, CROSS and SELF Joins with Exact Result Tables

What sample tables are we using?

Every example below runs on these two tables. They are small on purpose, so you can predict each result before you read it. We ran every query in this article in SQLite and copied the output exactly; the same SQL runs in PostgreSQL.

customers
customer_id	name	city
1	Aarav	Mumbai
2	Diya	Delhi
3	Kabir	Bengaluru
4	Meera	Pune

orders
order_id	customer_id	amount (₹)
101	1	1200
102	1	800
103	2	2500
104	5	900

Three things make this data useful for learning joins:

• Aarav has two orders, so he will appear twice in most joins.
• Kabir and Meera have no orders.
• Order 104 belongs to customer_id 5, who is not in the customers table (think of a deleted account or a guest checkout).

If you want to follow along, here is the setup script:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);

INSERT INTO customers VALUES
  (1,'Aarav','Mumbai'), (2,'Diya','Delhi'), (3,'Kabir','Bengaluru'), (4,'Meera','Pune');
INSERT INTO orders VALUES
  (101,1,1200), (102,1,800), (103,2,2500), (104,5,900);

Which SQL join should you use? A quick comparison

Join	Keeps	Rows on our data	Typical use
INNER JOIN	Only rows that match on both sides	3	Orders with valid customer details
LEFT JOIN	All left rows, matches from the right, NULL otherwise	5	All customers, with orders if any
RIGHT JOIN	All right rows, matches from the left, NULL otherwise	4	All orders, with customer details if any
FULL OUTER JOIN	All rows from both sides	6	Reconciling two lists
CROSS JOIN	Every combination (no condition)	4 × 4 = 16	Building grids, such as every size with every colour
SELF JOIN	A table joined to itself	Depends	Employee and manager in the same table

What does an INNER JOIN return?

An INNER JOIN returns only the pairs of rows where the join condition is true. Customers without orders and orders without customers both disappear.

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

customer_id	name	order_id	amount
1	Aarav	101	1200
1	Aarav	102	800
2	Diya	103	2500

Note that Aarav appears twice. A join returns one row per matching pair, not one row per customer. JOIN on its own means INNER JOIN in PostgreSQL, MySQL and SQLite.

What does a LEFT JOIN return?

A LEFT JOIN keeps every row from the table on the left of the keyword (here, customers). Where there is no matching order, the order columns are NULL.

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

customer_id	name	order_id	amount
1	Aarav	101	1200
1	Aarav	102	800
2	Diya	103	2500
3	Kabir	NULL	NULL
4	Meera	NULL	NULL

Order 104 is missing because its customer is not in the left table. The LEFT JOIN is the join analysts use most, because reports usually start from a full list (all customers, all products, all dates) and attach whatever activity exists.

How do you find customers with no orders?

Add a filter for NULL on a column from the right table that can never be NULL in real data, such as its key. This pattern is called an anti-join.

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

Result: 3 Kabir and 4 Meera. NOT EXISTS gives the same answer and is equally common in interviews.

What does a RIGHT JOIN return?

A RIGHT JOIN is the mirror image: it keeps every row from the right table (orders).

SELECT c.customer_id, c.name, o.order_id,
       o.customer_id AS order_customer_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

customer_id	name	order_id	order_customer_id	amount
1	Aarav	101	1	1200
1	Aarav	102	1	800
2	Diya	103	2	2500
NULL	NULL	104	5	900

Order 104 now appears with empty customer details, which is exactly how you spot "orphan" orders during a data-quality check. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, and MySQL's own manual recommends LEFT JOIN for portability. Most teams write LEFT JOINs only, because they are easier to read.

What does a FULL OUTER JOIN return?

A FULL OUTER JOIN keeps all rows from both tables: matches are paired, and unmatched rows on either side are padded with NULLs.

SELECT c.customer_id, c.name, o.order_id,
       o.customer_id AS order_customer_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY COALESCE(c.customer_id, o.customer_id), o.order_id;

customer_id	name	order_id	order_customer_id	amount
1	Aarav	101	1	1200
1	Aarav	102	1	800
2	Diya	103	2	2500
3	Kabir	NULL	NULL	NULL
4	Meera	NULL	NULL	NULL
NULL	NULL	104	5	900

Use it to reconcile two lists, for example a payments export against an orders table, where you care about mismatches on both sides.

How do you write a FULL OUTER JOIN in MySQL?

MySQL does not support FULL OUTER JOIN (its join grammar lists only INNER, CROSS, LEFT, RIGHT and NATURAL joins). Combine a LEFT JOIN with the unmatched rows of a RIGHT JOIN:

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION ALL
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

This returns the same six rows (the order of rows may differ unless you add ORDER BY). Use UNION ALL with the IS NULL filter rather than plain UNION: plain UNION would also collapse genuinely identical rows.

What does a CROSS JOIN return?

A CROSS JOIN has no join condition. It pairs every row of one table with every row of the other, so the row count is the product of the two. Joining our 4 customers to our 4 orders this way would give 16 meaningless rows. It is useful when you want every combination on purpose.

-- sizes: S, M        colours: Black, White, Blue
SELECT s.size, co.colour
FROM sizes s
CROSS JOIN colours co
ORDER BY s.size DESC, co.colour;

size	colour
S	Black
S	Blue
S	White
M	Black
M	Blue
M	White

2 sizes × 3 colours = 6 rows. Analysts use the same idea to build a "spine", such as every store × every date, and then LEFT JOIN sales onto it so that days with zero sales still show up as zero instead of vanishing.

What is a SELF JOIN?

A self join is an ordinary join where both sides are the same table, given two different aliases. The classic case is an employees table where manager_id points to another row in the same table.

employees
emp_id	name	manager_id
1	Nisha	NULL
2	Arjun	1
3	Farah	1
4	Vikram	2

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

employee	manager
Nisha	NULL
Arjun	Nisha
Farah	Nisha
Vikram	Arjun

We used a LEFT self join so that Nisha, who has no manager, stays in the result. With an INNER self join she would be dropped. Self joins also appear in interview questions such as "find customers who ordered on two consecutive days".

What are the most common SQL join mistakes?

1. Duplicate rows from joining two one-to-many tables

This is the mistake that most often produces wrong numbers in real reports. Suppose we also have a tickets table of support tickets: Aarav has 2 tickets and Diya has 1. Joining customers to orders and tickets in one step multiplies the rows:

SELECT c.name, SUM(o.amount) AS revenue, COUNT(t.ticket_id) AS tickets
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN tickets t ON t.customer_id = c.customer_id
GROUP BY c.name
ORDER BY c.name;

name	revenue	tickets
Aarav	4000	4
Diya	2500	1

Aarav's real revenue is ₹2,000 and he has 2 tickets, but his 2 orders × 2 tickets created 4 rows, so both numbers doubled. The fix is to aggregate each table to one row per customer before joining:

WITH o AS (
  SELECT customer_id, SUM(amount) AS revenue FROM orders GROUP BY customer_id
), t AS (
  SELECT customer_id, COUNT(*) AS tickets FROM tickets GROUP BY customer_id
)
SELECT c.name, o.revenue, COALESCE(t.tickets, 0) AS tickets
FROM customers c
JOIN o ON o.customer_id = c.customer_id
LEFT JOIN t ON t.customer_id = c.customer_id
ORDER BY c.name;

Result: Aarav 2000 and 2 tickets, Diya 2500 and 1 ticket. A good habit: before and after every join, check the row count and ask "what is one row of this result?"

2. Filtering the right table in WHERE, which silently turns a LEFT JOIN into an INNER JOIN

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 1000;

This returns only Aarav (101) and Diya (103). Kabir and Meera had NULL amounts, and NULL > 1000 is not true, so WHERE removed them. If you want all customers plus only their large orders, move the condition into the ON clause:

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 1000;

Now you get four rows: Aarav 101, Diya 103, Kabir NULL and Meera NULL.

3. Joining on the wrong or incomplete key

If a table's key is two columns (say store_id and date) and you join on only one, every row matches many rows. Check the grain of each table first. Watch for mismatched data types too, such as an ID stored as text in one table and as a number in the other.

4. Forgetting that NULL never equals NULL

ON a.col = b.col never matches two NULLs. If both tables have missing keys, those rows will not join. Decide deliberately whether that is what you want.

How can you practise SQL joins?

Recreate the tables above and, before running each query, write down the rows you expect. Then change one thing at a time: add a second order for Diya, delete Meera, add a NULL customer_id. Predicting row counts is the skill interviewers test.

When you are comfortable, work through our SQL interview questions with answers, which use joins alongside GROUP BY, CTEs and window functions. For a structured plan, see how to learn SQL in 30 days or our guide to choosing a SQL course in India.

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?

An INNER JOIN returns only rows that match in both tables. A LEFT JOIN returns every row from the left table and fills the right-table columns with NULL where there is no match. With 4 customers and 3 matching orders, an INNER JOIN can return fewer rows than a LEFT JOIN, which also keeps customers without orders.

Why does my SQL join return duplicate rows?

A join returns one row per matching pair. If one customer has two orders, that customer appears twice. If you join two one-to-many tables at once, such as orders and tickets, the rows multiply. Aggregate each table to one row per key before joining, and check row counts after every join.

Is JOIN the same as INNER JOIN?

Yes. In PostgreSQL, MySQL, SQLite and SQL Server, writing JOIN without a type means INNER JOIN. Writing INNER JOIN explicitly makes the query easier to read.

Does MySQL support FULL OUTER JOIN?

No. MySQL supports INNER, CROSS, LEFT, RIGHT and NATURAL joins but not FULL OUTER JOIN. You can get the same result with a LEFT JOIN, then UNION ALL, then a RIGHT JOIN filtered to rows where the left key IS NULL.

When would you use a CROSS JOIN?

Use a CROSS JOIN when you want every combination of two lists, such as every size with every colour, or every store with every date. Analysts often cross join stores and dates to build a complete grid, then LEFT JOIN sales so that days with no sales show as zero.

Sources and methodology

All example queries were run in SQLite 3.54 on the sample tables shown, and the result tables are copied from that output. The same SQL was written to also run in PostgreSQL.

• MySQL 8.4 Reference Manual, JOIN clause (supported join types; recommendation to prefer LEFT JOIN), checked September 2026.
• SQLite, SELECT statement and join operators, checked September 2026.
• PostgreSQL documentation, Table expressions: joined tables, checked September 2026.
• ISS: Data & Business Intelligence program page (SQL weeks).

The customer, order and employee names are made-up sample data. The practice advice is ISS editorial guidance.

Next steps

Joins are covered in Week 4 of the Data & Business Intelligence program, alongside CTEs, window functions and cohort analysis. If you want a structured path from SQL basics to dashboards, review the curriculum and grab the free Data Analyst Starter Kit offered on this page.

Applying is free, and you pay only after accepting an offer. Apply here.