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

1. Quick Answer

A SQL JOIN combines rows from two tables using a related column.

The primary difference between the four common JOIN types is which unmatched rows are included in the result.

• INNER JOIN returns only rows that match in both tables.
• LEFT JOIN returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN returns all rows from the right table and matching rows from the left table.
• FULL JOIN returns all rows from both tables, including unmatched rows from either side.

2. Comparison at a Glance

3. INNER JOIN

An INNER JOIN returns only the records that exist in both tables.

Example:

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

Students without matching records in the marks table are not included in the result.

Use INNER JOIN when you need:

• Only matching records
• Common data from both tables
• Records that exist in both datasets

4. LEFT JOIN

A LEFT JOIN returns every record from the left table and matching records from the right table.

If no matching record exists, SQL returns NULL for the right table columns.

Example:

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

Every student appears in the result, even if no marks are available.

Finding Records That Exist in One Table but Not Another

A common SQL interview technique is combining LEFT JOIN with IS NULL.

Example:

SELECT students.student_name
FROM students
LEFT JOIN marks
ON students.student_id = marks.student_id
WHERE marks.student_id IS NULL;

This query returns students who do not have matching records in the marks table.

Alternative approaches include:

• NOT IN
• NOT EXISTS

These techniques are covered in detail in the complete SQL JOIN tutorials.

5. RIGHT JOIN & FULL JOIN

RIGHT JOIN

A RIGHT JOIN is the opposite of a LEFT JOIN.

It returns:

• Every record from the right table
• Matching records from the left table
• NULL values where no matching left-side record exists

In practice, many developers simply reverse the table order and use a LEFT JOIN instead.

FULL JOIN

A FULL JOIN returns every row from both tables.

If a matching record does not exist, SQL fills the missing columns with NULL values.

Use a FULL JOIN when you need a complete view of both datasets, including all matched and unmatched records.

6. Which JOIN Should You Use?

Choose the JOIN type based on your requirement.

For complete explanations and additional examples, refer to the Complete SQL for Testers Guide.

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?

An INNER JOIN returns only records that exist in both tables.

A LEFT JOIN returns all records from the left table and matching records from the right table. If no match exists, the right-side columns contain NULL values.

How do you find records in one table but not another?

The most common approach is to use a LEFT JOIN together with:

WHERE right_table.id IS NULL

Alternative techniques include:

• NOT IN
• NOT EXISTS

What is the difference between INNER JOIN and INTERSECT?

Both are used to identify common records.

• INNER JOIN matches records using a join condition and can return columns from both tables.
• INTERSECT compares complete result sets and returns only common rows.

When should you use a FULL JOIN?

A FULL JOIN is useful when you need every record from both tables, including records that do not have matching values.

Is RIGHT JOIN necessary?

In many projects, developers simply reverse the table order and use a LEFT JOIN, making RIGHT JOIN less common in practice.