SQL JOINS | Introduction to INNER, LEFT & RIGHT JOIN

SQL JOINS

In real-world applications, data is not stored in a single table.

For example:

Employee details in one table

Department details in another table

Salary or project details in a separate table

This design is intentional. It avoids data duplication and keeps databases clean and efficient. But when we want a complete view, we often need data from multiple tables together.

This is where JOINs come into the picture.

What Is a JOIN in SQL?

A JOIN in SQL is used to combine rows from two or more tables based on a related column between them.

JOIN allows SQL to fetch related data stored across different tables and present it as a single result set.

Real-World Analogy

Think of:

A student ID card (student table)

A marksheet (marks table)

Both contain the same student ID.

JOIN uses this common field to connect information and display a complete picture.

Syntax

SELECT columns
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;

Example: employees Tables

emp_id	name	dept_id
1	Amit	101
2	Neha	102
3	Rahul	101
4	Priya	103

departments

dept_id	dept_name
101	IT
102	HR
103	Finance
104	Marketing

1. INNER JOIN

Why INNER JOIN Is Needed

Often, we want only matching records from both tables.

Example:

Employees who are assigned to a valid department

Orders that belong to existing customers

INNER JOIN helps fetch only common data.

What INNER JOIN Does

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

If there is no match, the row is excluded.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON condition;

Example: Employee Name with Department Name

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;

Output

name	dept_name
Amit	IT
Neha	HR
Rahul	IT
Priya	Finance

Only matching records are shown.

2. LEFT JOIN

Why LEFT JOIN Is Needed

Sometimes we want:

All records from the main (left) table

Even if there is no matching record in the other table

Example

All employees, even those not assigned to any department

All customers, even if they have not placed any order

What LEFT JOIN Does

A LEFT JOIN returns:

All rows from the left table

Matching rows from the right table

NULL for unmatched rows

Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON condition;

Example: All Employees with Department Info

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;

Employees without a department still appear, with NULL values.

3. RIGHT JOIN

Why RIGHT JOIN Is Needed

RIGHT JOIN is used when:

All records from the right table are required

Even if there are no matching records in the left table

Example

All departments, even if no employee is assigned

What RIGHT JOIN Does

A RIGHT JOIN returns:

All rows from the right table

Matching rows from the left table

NULL where no match exists

Syntax

SELECT columns
FROM table1
RIGHT JOIN table2
ON condition;

Example: All Departments with Employee Names

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d
ON e.dept_id = d.dept_id;

Departments with no employees still appear.

Comparison Between INNER, LEFT and RIGHT JOIN

JOIN Type	What It Returns
INNER JOIN	Only matching rows
LEFT JOIN	All rows from left table
RIGHT JOIN	All rows from right table

Important Points About JOINs

JOIN condition is specified using ON

JOINs are usually based on primary-foreign key relationships

Table aliases improve readability

JOINs are evaluated during the FROM clause execution

Two Minute Drill

JOIN combines data from multiple tables
INNER JOIN returns only matching rows
LEFT JOIN returns all left table rows
RIGHT JOIN returns all right table rows
Unmatched records show NULL
JOIN works using a common column