What is a Join? List its different types

SQL
#1

The SQL Join clause allows you to combine rows from different tables in a database by matching values in a shared column. This enables you to retrieve data from multiple tables using a single query.

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 the left or right table contains a match.
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

Code Runner

Copy the below queries in the code runner to see the result

SELECT e.emp_name, d.dept_name
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;

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

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

SELECT *
FROM Employee e
FULL JOIN Department d ON e.dept_id = d.dept_id;