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 |












