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

Department Table

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

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

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

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

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

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

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

Explanation

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

Difference Between SQL Joins