Joins in SQL

SQL Joins in SQL

Introduction

SQL Joins are used to combine data from two or more tables based on a related column. They help retrieve meaningful information stored across multiple tables.

Employees Table

emp_id	emp_name	dept_id
101	Kumar	1
102	Priya	2
103	Raja	1
104	Anu	3

Department Table

dept_id	dept_name
1	ARUL
2	HARINI
4	SIMBU
5	DIVYA

---

1. INNER JOIN

An INNER JOIN returns only the matching records from both tables.

Query

SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
INNER JOIN Department d
ON e.dept_id = d.dept_id;

Output

emp_id	emp_name	dept_name
101	Kumar	ARUL
102	Priya	HARINI
103	Raja	ARUL

Explanation

Only matching department IDs are displayed. Anu is excluded because department 3 does not exist in the Department table.

---

2. LEFT JOIN

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

Query

SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
LEFT JOIN Department d
ON e.dept_id = d.dept_id;

Output

emp_id	emp_name	dept_name
101	Kumar	ARUL
102	Priya	HARINI
103	Raja	ARUL
104	Anu	NULL

Explanation

All employees are displayed. Since department 3 does not exist, Anu's department appears as NULL.

---

3. RIGHT JOIN

A RIGHT JOIN returns all records from the right table and matching records from the left table.

Query

SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
RIGHT JOIN Department d
ON e.dept_id = d.dept_id;

Output

emp_id	emp_name	dept_name
101	Kumar	ARUL
103	Raja	ARUL
102	Priya	HARINI
NULL	NULL	SIMBU
NULL	NULL	DIVYA

Explanation

All departments are displayed. SIMBU and DIVYA have no employees, so employee columns contain NULL values.

---

4. FULL OUTER JOIN

A FULL OUTER JOIN returns all records from both tables.

Query

SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
FULL OUTER JOIN Department d
ON e.dept_id = d.dept_id;

Output

emp_id	emp_name	dept_name
101	Kumar	ARUL
102	Priya	HARINI
103	Raja	ARUL
104	Anu	NULL
NULL	NULL	SIMBU
NULL	NULL	DIVYA

Explanation

All employees and all departments are displayed. Unmatched rows contain NULL values.

---

5. CROSS JOIN

A CROSS JOIN returns every possible combination of rows from both tables.

Query

SELECT e.emp_name, d.dept_name
FROM Employees e
CROSS JOIN Department d;

Output

emp_name	dept_name
Kumar	ARUL
Kumar	HARINI
Kumar	SIMBU
Kumar	DIVYA
Priya	ARUL
Priya	HARINI
Priya	SIMBU
Priya	DIVYA
Raja	ARUL
Raja	HARINI
Raja	SIMBU
Raja	DIVYA
Anu	ARUL
Anu	HARINI
Anu	SIMBU
Anu	DIVYA

Explanation

Every employee is combined with every department.

Formula:

Rows in Employees × Rows in Department

4 × 4 = 16 Rows

---

6. SELF JOIN

A SELF JOIN joins a table with itself.

Employee Table

emp_id	emp_name	manager_id
1	Arul	NULL
2	Kumar	1
3	Priya	1
4	Raja	2

Query

SELECT e.emp_name AS Employee,
       m.emp_name AS Manager
FROM Employee e
LEFT JOIN Employee m
ON e.manager_id = m.emp_id;

Output

Employee	Manager
Arul	NULL
Kumar	Arul
Priya	Arul
Raja	Kumar

Explanation

The Employee table is joined with itself to find each employee's manager.

---

Difference Between SQL Joins

Join Type	Result
INNER JOIN	Matching rows only
LEFT JOIN	All left table rows + matching right rows
RIGHT JOIN	All right table rows + matching left rows
FULL OUTER JOIN	All rows from both tables
CROSS JOIN	Every possible combination
SELF JOIN	Table joined with itself

---