SQL Joins Explained: INNER, LEFT, RIGHT, FULL (With Examples for Testers)

What Is a JOIN in SQL?

A JOIN is used to combine rows from two or more tables based on a related column between them. It allows you to retrieve related data stored across multiple tables in a single query.

Example

Imagine you have:

• A Car Toys table containing toy names and colors.
• An Owners table containing owner names and the toy they own.

If you want to know which owner owns which toy, you match both tables using a common column, such as toy_id. This process is called a JOIN.

Why Do We Need JOINs?

In relational databases, data is often split across multiple tables to reduce duplication. This process is known as normalization.

When you need related information from multiple tables, a JOIN combines the data into a single result.

For example:

• Employee details are stored in one table.
• Department details are stored in another table.

A JOIN allows you to retrieve an employee along with their department information in one query.

Types of JOINs

There are four main types of SQL JOINs.

INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables.

Example

Assume you have:

• students(student_id, student_name)
• marks(student_id, marks)

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

Sample Output

Explanation

Only students who have matching records in the marks table are returned.

Students without marks are excluded.

LEFT JOIN

A LEFT JOIN returns:

• All rows from the left table.
• Matching rows from the right table.
• NULL for right-table columns when no match exists.

Example

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

Explanation

Every student appears in the result.

If a student has no corresponding row in the marks table, the marks column contains NULL.

This is a common technique for identifying missing related data.

RIGHT JOIN

A RIGHT JOIN returns:

• All rows from the right table.
• Matching rows from the left table.
• NULL where no matching row exists in the left table.

Example

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

Explanation

Every row from the marks table appears.

If a marks record has no matching student, the student columns contain NULL.

FULL JOIN

A FULL JOIN (or FULL OUTER JOIN) returns every row from both tables.

If no matching row exists in either table, SQL fills the missing columns with NULL.

Example

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

Explanation

The result includes:

• Matching rows
• Students without marks
• Marks without matching students

Finding Common Records in Two Tables

There are two common ways to find records that exist in both tables.

Method 1: Using INNER JOIN

SELECT t1.emp_id, t1.emp_name
FROM Employees_2023 t1
INNER JOIN Employees_2024 t2
ON t1.emp_id = t2.emp_id;

How It Works

Only employees whose emp_id exists in both tables are returned.

Method 2: Using INTERSECT

Note: INTERSECT is supported in databases such as SQL Server and PostgreSQL but not in MySQL.

SELECT emp_id, emp_name
FROM Employees_2023

INTERSECT

SELECT emp_id, emp_name
FROM Employees_2024;

How It Works

INTERSECT returns only the rows that are common to both queries.

Testing Scenario

To find employees who were present in both 2023 and 2024, you can use:

• INNER JOIN on emp_id
• INTERSECT (if your database supports it)

Finding Records in One Table but Not Another

Finding unmatched records is a very common SQL interview question.

There are three popular approaches.

Method 1: LEFT JOIN with IS NULL

SELECT s.student_id, s.student_name
FROM Students s
LEFT JOIN GraduatedStudents g
ON s.student_id = g.student_id
WHERE g.student_id IS NULL;

Explanation

• SQL joins both tables.
• Rows with no matching record in GraduatedStudents have NULL.
• The WHERE clause filters those unmatched rows.

Method 2: Using NOT IN

SELECT student_id, student_name
FROM Students
WHERE student_id NOT IN (
    SELECT student_id
    FROM GraduatedStudents
);

Explanation

This returns students whose IDs do not appear in the GraduatedStudents table.

Method 3: Using NOT EXISTS

SELECT s.student_id, s.student_name
FROM Students s
WHERE NOT EXISTS (
    SELECT 1
    FROM GraduatedStudents g
    WHERE g.student_id = s.student_id
);

Explanation

NOT EXISTS checks whether a matching row exists.

If no matching row is found, the student is returned.

Common Use Cases

These techniques are frequently used to identify:

• Customers who have not placed orders.
• Students who have not graduated.
• Employees without departments.
• Products that have never been sold.
• Users who have never logged in.

INNER JOIN vs INTERSECT

Although both can return common data, they work differently.

📚 Official documentation: MySQL manual: JOIN clause

LEFT JOIN vs RIGHT JOIN

Both are outer joins; the only difference is which table keeps all its rows.

-- customers:  1 Asha | 2 Ravi | 3 Meera
-- orders:     101 → customer 1 | 102 → customer 1 | 103 → customer 4

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Asha 101 | Asha 102 | Ravi NULL | Meera NULL   (every customer)

SELECT c.name, o.id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;
-- Asha 101 | Asha 102 | NULL 103                 (every order)

• LEFT JOIN keeps every row of the first (left) table and fills NULL where there's no match.
• RIGHT JOIN keeps every row of the second (right) table.
• Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, which is why most teams only use LEFT JOIN.

In testing, the NULL rows are the interesting ones. The RIGHT JOIN above found order 103 pointing to a customer that doesn't exist, which is an orphan record and a real bug. MySQL has no FULL OUTER JOIN; get the same result with a LEFT JOIN UNION a RIGHT JOIN.

FAQs

1. What Is a JOIN in SQL?

A JOIN combines rows from two or more tables based on a related column. It allows you to retrieve related data stored in multiple tables using a single query.

2. Why Do We Need JOINs?

JOINs are needed because relational databases store related information in separate tables to reduce redundancy through normalization. A JOIN combines that related data whenever a complete view is required.

3. What Are the Main Types of JOINs?

The four main types of SQL JOINs are:

• INNER JOIN – Returns only matching rows from both tables.
• LEFT JOIN (LEFT OUTER JOIN) – Returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN) – Returns all rows from the right table and matching rows from the left table.
• FULL JOIN (FULL OUTER JOIN) – Returns all rows from both tables, using NULL where no match exists.

4. How Do You Find Common Records in Two Tables?

You can find common records by:

• Using an INNER JOIN on the matching column.
• Using the INTERSECT operator (if supported by your database).

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

Common approaches include:

• LEFT JOIN with an IS NULL condition.
• NOT IN subquery.
• NOT EXISTS subquery.

These methods return records that exist in one table but not in the other.

6. What Is the Difference Between INNER JOIN and INTERSECT?

INNER JOIN

• Combines related columns from two tables.
• Returns matching rows based on a join condition.

INTERSECT

• Returns only the rows common to two SELECT statements.
• Requires both queries to return the same number of columns with compatible data types.
• Does not combine columns from different tables.