What is a Join? List its different types

The SQL Join clause is used to combine records (rows) from two or more tables in a SQL database based on a related column between the two.

Employee Table

emp_id emp_name alary dept_id manager_id
E1 Rahul 15000 D1 M1
E2 Manoj 15000 D1 M1
E3 James 55000 D2 M2
E4 Michael 25000 D2 M2
E5 Ali 20000 D10 M3
E6 Robin 35000 D10 M3

Department Table

dept_id dept_name
D1 IT
D2 HR
D3 Finance
D4 Admin

There are four different types of JOINs in SQL:

  • (INNER) JOIN: Retrieves records that have matching values in both tables involved in the join. This is the widely used join for queries.
SELECT e.emp_name, d.dept_name
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;

Output

emp_name dept_name
Rahul IT
Manoj IT
James HR
Michael HR
  • LEFT (OUTER) JOIN: Retrieves all the records/rows from the left and the matched records/rows from the right table.
SELECT e.emp_name, d.dept_name
FROM Employee e
LEFT JOIN Department d ON e.dept_id = d.dept_id;

Output

emp_name dept_name
Rahul IT
Manoj IT
James HR
Michael HR
Ali Null
Robin Null
  • RIGHT (OUTER) JOIN: Retrieves all the records/rows from the right and the matched records/rows from the left table.
SELECT e.emp_name, d.dept_name
FROM Employee e
RIGHT JOIN Department d ON e.dept_id = d.dept_id;

Output

emp_name dept_name
Rahul IT
Manoj IT
James HR
Michael HR
Null Finance
Null Admin
  • FULL (OUTER) JOIN: Retrieves all the records where there is a match in either the left or right table.
SELECT *
FROM Employee e
FULL JOIN Department d ON e.dept_id = d.dept_id;

Output

emp_name dept_name
Rahul IT
Manoj IT
James HR
Michael HR
Ali Null
Robin Null
Null Finance
Null Admin