SQL Joins Explained With Examples: INNER, LEFT, RIGHT, FULL and More

A SQL join combines rows from two tables using a column they share, such as customer_id. An INNER JOIN keeps only the rows that match in both tables. A LEFT JOIN keeps every row from the left table and fills in NULL where the right table has no match. RIGHT JOIN is the mirror image, FULL OUTER JOIN keeps everything from both sides, and CROSS JOIN pairs every row with every other row. Most analysis work uses INNER and LEFT joins, and most join bugs come from duplicate keys that multiply rows without warning.

I'm Gulab Chand Tejwani. I teach SQL at TechNet Consultancy in Vallabh Vidyanagar, Anand, and online, and I'm a Microsoft Certified: Power BI Data Analyst Associate and a Microsoft Certified Trainer. Joins are the topic where I see the biggest jump in a learner's confidence, once they stop memorising Venn diagrams and start looking at actual rows. So that's what we'll do.

The two tables we'll use

Imagine a small coaching institute. It has a students table and an enrolments table.

students:

• 1, Riya, Anand
• 2, Karan, Nadiad
• 3, Meera, Vadodara
• 4, Arjun, Anand

enrolments (enrolment_id, student_id, course):

• 101, 1, SQL
• 102, 1, Power BI
• 103, 2, Python
• 104, 5, SQL

Look closely. Riya (1) has two enrolments. Meera (3) and Arjun (4) have none. Enrolment 104 belongs to student 5, who doesn't exist in students. Maybe the record was deleted, maybe it was a typing mistake. Real data looks like this all the time.

What does an INNER JOIN return?

SELECT s.name, e.course
FROM students s
INNER JOIN enrolments e ON e.student_id = s.student_id;

Result:

• Riya, SQL
• Riya, Power BI
• Karan, Python

Only rows that match on both sides survive. Meera and Arjun drop out because they have no enrolment. Enrolment 104 drops out because student 5 isn't there.

Two things to notice. First, Riya appears twice, because she matches two rows. A join returns one row per matching pair, not one row per student. Second, nothing tells you rows were dropped. INNER JOIN fails quietly, and that's why I ask learners to count rows before and after every join.

Writing just JOIN means INNER JOIN in every major database.

What does a LEFT JOIN return?

SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id;

Result:

• Riya, SQL
• Riya, Power BI
• Karan, Python
• Meera, NULL
• Arjun, NULL

Every student is kept. Where there's no enrolment, the enrolment columns are NULL. This is the join you'll use most as an analyst, because business questions usually start from one main list ("all customers", "all products", "all stores") and add details where they exist.

	INNER JOIN	LEFT JOIN
Rows returned	3	5
Riya (2 enrolments)	2 rows	2 rows
Meera, Arjun (none)	Dropped	Kept, course is NULL
Orphan enrolment 104	Dropped	Dropped
Use when	You only want matched pairs	You start from a full list

How do I find rows with no match?

Add a filter for the NULL:

SELECT s.name
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
WHERE e.enrolment_id IS NULL;

Result: Meera, Arjun.

This is called an anti-join, and it answers a whole family of useful questions: customers who never ordered, products that never sold, employees with no training record. NOT EXISTS does the same thing and some people find it clearer:

SELECT s.name
FROM students s
WHERE NOT EXISTS (
  SELECT 1 FROM enrolments e WHERE e.student_id = s.student_id
);

Be careful with NOT IN here. If the subquery returns even one NULL, NOT IN returns no rows at all. NOT EXISTS doesn't have that trap.

Why does a WHERE clause turn my LEFT JOIN into an INNER JOIN?

This is the most common join bug I see in class. Suppose you want all students, plus their SQL enrolment if they have one:

-- Wrong
SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
WHERE e.course = 'SQL';

Result: Riya, SQL. Only one row. The WHERE clause runs after the join, and for Meera and Arjun e.course is NULL, so NULL = 'SQL' isn't true and they get filtered away. You've written an INNER JOIN by accident.

Move the condition into the ON clause:

-- Right
SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e
  ON e.student_id = s.student_id
 AND e.course = 'SQL';

Result: Riya, SQL. Karan, NULL. Meera, NULL. Arjun, NULL. All four students, with the SQL course where it exists.

Rule of thumb: conditions about the right-hand table go in ON if you want to keep unmatched left rows.

What do RIGHT JOIN and FULL OUTER JOIN do?

A RIGHT JOIN keeps every row from the right table:

SELECT s.name, e.course
FROM students s
RIGHT JOIN enrolments e ON e.student_id = s.student_id;

Result: Riya, SQL. Riya, Power BI. Karan, Python. NULL, SQL.

That last row is enrolment 104 with no student. In practice most people rewrite RIGHT JOINs as LEFT JOINs by swapping the table order, because reading left to right is easier. You'll rarely need RIGHT JOIN, but you should recognise it.

A FULL OUTER JOIN keeps everything from both sides:

SELECT s.name, e.course
FROM students s
FULL OUTER JOIN enrolments e ON e.student_id = s.student_id;

Result: the three matches, plus Meera and Arjun with NULL courses, plus the orphan SQL enrolment with a NULL name. Six rows.

FULL OUTER JOIN is handy for reconciliation, for example comparing a list of invoices from the accounts system with payments from the bank and seeing what is missing on each side. MySQL doesn't support FULL OUTER JOIN directly; you combine a LEFT JOIN and a RIGHT JOIN with UNION.

What is a CROSS JOIN for?

SELECT s.name, c.course
FROM students s
CROSS JOIN (VALUES ('SQL'), ('Power BI'), ('Python')) AS c(course);

Four students times three courses gives twelve rows: every student paired with every course. That sounds useless until you need a complete grid, such as every store for every day of the month, so that days with zero sales show up as zero instead of disappearing. You then LEFT JOIN the actual sales onto that grid.

The VALUES list above works in PostgreSQL and SQL Server. In MySQL you'd use a small table or a UNION of SELECTs.

What is a self join?

A self join is a table joined to itself. The classic example is an employees table where each row has a manager_id pointing to another employee:

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

The aliases e and m make it read like two different tables. LEFT JOIN keeps the person at the top, who has no manager.

Why did my join suddenly multiply rows?

Because the join key wasn't unique on at least one side.

Say you join orders to payments on order_id, and some orders were paid in two instalments. Each of those orders now appears twice, and if you then SUM(order_amount), you've counted those orders twice. The total looks believable and is wrong.

How to protect yourself:

1. Before joining, check whether the key is unique: SELECT order_id, COUNT(*) FROM payments GROUP BY order_id HAVING COUNT(*) > 1;
2. Count rows before and after the join. If the count went up and you didn't expect it, stop.
3. If one side has many rows per key, aggregate it first in a CTE (total payment per order), then join.

This one habit will save you from more embarrassing meetings than any other SQL skill. I'm not exaggerating; it's the error I've had to fix most often in other people's reports.

A worked fix for multiplied rows

Here's the bug in full, using an orders table and a payments table where order 7 was paid in two instalments of ₹500.

-- Wrong: order 7's amount is counted twice
SELECT SUM(o.order_amount) AS total_ordered,
       SUM(p.amount)       AS total_paid
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id;

If order 7 is worth ₹1,000, total_ordered now includes it twice, because the join produced two rows for it. The fix is to aggregate payments first so the join is one-to-one:

WITH paid AS (
  SELECT order_id, SUM(amount) AS amount_paid
  FROM payments
  GROUP BY order_id
)
SELECT SUM(o.order_amount)              AS total_ordered,
       SUM(COALESCE(p.amount_paid, 0))  AS total_paid
FROM orders o
LEFT JOIN paid p ON p.order_id = o.order_id;

Now each order appears once, and unpaid orders count as zero paid instead of disappearing.

Does join order or performance matter?

For INNER JOINs, the database's optimiser usually picks the order, so you can write them in whatever order reads best. For LEFT JOINs, order changes the meaning, so write them deliberately.

For speed on large tables, make sure the join columns are indexed, join on columns of the same data type (joining an integer to a text column can stop an index being used), and filter early. If you want to see what the database is doing, run EXPLAIN in PostgreSQL or MySQL, or look at the execution plan in SQL Server.

Which join should I use? A quick guide

When a question arrives, I ask myself three things before writing the join.

• What's my starting list? If the answer must include every customer, product or date, that table goes on the left of a LEFT JOIN.
• Do I only want pairs that exist? Then INNER JOIN, and say so in your notes so nobody wonders where the other rows went.
• Am I comparing two lists to find gaps on both sides? That's FULL OUTER JOIN, or two anti-joins if your database doesn't support it.

How do joins show up in interviews?

Very often. Typical fresher questions include "what's the difference between INNER and LEFT JOIN", "find customers with no orders" and "why did this query return more rows than expected". Interviewers like to give you two small tables, like the ones above, and ask you to write the result by hand. Practise doing exactly that.

For more query patterns built on joins, see SQL for data analysis: 15 queries. Joins are part of stage two in my data analyst roadmap. We drill them in the SQL training, in the Vallabh Vidyanagar classroom or live online, and the SQL interview preparation page focuses on the interview side.

FAQ

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table, and fills the right table's columns with NULL where there's no match.

Is JOIN the same as INNER JOIN?

Yes. In PostgreSQL, MySQL, SQL Server and Oracle, writing JOIN on its own means INNER JOIN.

Why does my join return duplicate rows?

The join column isn't unique on one side, so each row matches several rows on the other. Check for duplicate keys with GROUP BY and HAVING COUNT(*) > 1, and aggregate the many side before joining if you need one row per key.

Which join is used most in data analysis?

LEFT JOIN and INNER JOIN cover most real work. LEFT JOIN is especially common because analysts usually start from a full list (customers, products, dates) and add details where they exist.

Does MySQL support FULL OUTER JOIN?

No, not directly. You can get the same result by combining a LEFT JOIN and a RIGHT JOIN with UNION.