SQL Joins Compared: INNER vs LEFT vs RIGHT vs FULL (With Examples)

SQL Joins Compared

Quick Answer

A JOIN combines rows from two tables using a related column. The join types differ in which unmatched rows they keep:

• INNER JOIN — only rows that match in both tables.
• LEFT JOIN — every row from the left table, plus matches (NULL where there's no match).
• RIGHT JOIN — every row from the right table, plus matches.
• FULL OUTER JOIN — every row from both tables.

The Sample Data

All examples use these two tables. Note that Meera has no marks, and there's a mark for student 4, who doesn't exist — the kind of orphan record testers look for.

INNER JOIN — Only Matches

SELECT s.student_name, m.subject, m.marks
FROM students s
INNER JOIN marks m ON s.student_id = m.student_id;

Meera (no marks) and student 4's mark (no student) are both excluded.

Notice Asha appears twice — a one-to-many join repeats the "one" side once per match, which surprises people who expect one row per student.

LEFT JOIN — Everything From the Left

SELECT s.student_name, m.subject, m.marks
FROM students s
LEFT JOIN marks m ON s.student_id = m.student_id;

Every student appears; Meera gets NULL values because she has no marks.

RIGHT JOIN — Everything From the Right

SELECT s.student_name, m.student_id, m.subject, m.marks
FROM students s
RIGHT JOIN marks m ON s.student_id = m.student_id;

Every mark appears, including the orphan for student 4.

In practice, most people swap the table order and write a LEFT JOIN instead — the result is the same.

FULL OUTER JOIN — Everything From Both

MySQL and MariaDB don't support FULL OUTER JOIN directly; combine a LEFT and a RIGHT join with UNION:

SELECT s.student_name, m.student_id, m.subject
FROM students s
LEFT JOIN marks m ON s.student_id = m.student_id

UNION

SELECT s.student_name, m.student_id, m.subject
FROM students s
RIGHT JOIN marks m ON s.student_id = m.student_id;

PostgreSQL, SQL Server and Oracle support FULL OUTER JOIN … ON … directly.

Two More You Should Know

• CROSS JOIN — every row of one table combined with every row of the other (3 students × 4 marks = 12 rows). Useful for generating test-data combinations; dangerous by accident.
• SELF JOIN — a table joined to itself, e.g. employees and their managers:

FROM employees e
JOIN employees m ON e.manager_id = m.id

Finding Missing and Orphan Records ⭐

This is where joins earn their place in a tester's toolkit.

Students with no marks

-- Students with no marks
SELECT s.student_name
FROM students s
LEFT JOIN marks m ON s.student_id = m.student_id
WHERE m.student_id IS NULL;
-- → Meera

Orphan marks

-- Orphan marks: marks for students that don't exist
SELECT m.*
FROM marks m
LEFT JOIN students s ON s.student_id = m.student_id
WHERE s.student_id IS NULL;
-- → student_id 4, Maths, 90

NOT EXISTS gives the same results. Be careful with NOT IN: if the subquery returns any NULL, it can produce unexpected results because of SQL's three-valued logic.

The WHERE vs ON Trap

Filtering the right-hand table in WHERE can silently turn a LEFT JOIN into an INNER JOIN:

-- ❌ Meera disappears: the NULL row fails the WHERE condition
SELECT s.student_name, m.subject
FROM students s
LEFT JOIN marks m ON s.student_id = m.student_id
WHERE m.subject = 'Maths';
-- → Asha, Ravi

To keep every student, put the condition in the ON clause:

-- ✅ Keep every student
SELECT s.student_name, m.subject
FROM students s
LEFT JOIN marks m
    ON s.student_id = m.student_id
    AND m.subject = 'Maths';

-- → Asha Maths, Meera NULL, Ravi Maths

Which JOIN Should You Use?

How Testers Use Joins

• Verify that a UI order shows up with the right items and payment (orders ⋈ order_items ⋈ payments).
• Find orphan records left behind after deletes — a common data-integrity defect.
• Check reports: does a count on screen match the joined data in the database?
• Find users without roles, products without prices, or bookings without a customer.

Practice under time pressure in the SQL Interview Simulator.

Full tutorial: SQL Joins Explained.

📚 Official documentation: MySQL Reference Manual

Frequently Asked Questions

What Is the Difference Between INNER JOIN and LEFT JOIN?

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

How Do You Find Records in One Table but Not Another?

LEFT JOIN the second table and filter:

WHERE second_table.key IS NULL

Alternatively, use NOT EXISTS.

Does MySQL Support FULL OUTER JOIN?

No. Combine a LEFT JOIN and a RIGHT JOIN with UNION.

Why Does My LEFT JOIN Return Fewer Rows Than Expected?

A condition on the right-hand table in WHERE removes the NULL rows. Move that condition into the ON clause when you need to preserve unmatched left-side rows.

Why Does a JOIN Return Duplicate Rows?

One-to-many relationships cause the "one" row to repeat once for every matching "many" row. Aggregate or use DISTINCT if you need one row per entity, depending on the requirement.

INNER JOIN vs INTERSECT?

INNER JOIN matches rows based on a join condition and can return columns from both tables. INTERSECT compares two result sets and returns rows common to both result sets.

Is RIGHT JOIN Necessary?

Not really. Swapping the table order and using a LEFT JOIN gives the same result, which is why RIGHT JOIN is less common in practice.

Related

• The Complete SQL for Testers Guide
• Top 25 SQL Interview Questions for Testers
• Subqueries & Nested Queries
• SQL Cheat Sheet for Testers