SQL Joins

A Join clause is used to combine two or more tables in a database by merging the records from the tables based on a related column between them.

Example JOIN using two tables: employees and departments

employees table:

id	emp_name	department_id	joining_date
1	Anuj kumar	101	2025-01-01
2	Rahul Singh	102	2025-02-16
3	John Doe	102	2025-02-25
4	Alex	104	2025-04-20
5	Alice	null	2025-05-24
6	Garima	110	2025-05-28

departments tables:

Example

SELECT employees.id,employees.emp_name, departments.department_name
FROM employees
JOIN departments
ON employees.department_id = departments.id;

SELECT employees.id,employees.emp_name, departments.department_name

FROM employees

JOIN departments

ON employees.department_id = departments.id;

Output will be:

Only rows where department_id matches departments.id are shown.

Alice is excluded because department_id is NULL.

Garima is also excluded because 110 does not exist in the departments table.

There are five common types of SQL JOINs Query:

Inner Join: Returns only the rows with matching values in both tables.

Right Outer Join: Returns all rows from the right table and matched rows from the left table; unmatched left-side values are NULL.

Left Outer Join: Returns all rows from the left table and matched rows from the right table; unmatched right-side values are NULL.

Full outer Join: Returns all rows when there is a match in one of the tables; unmatched rows from both sides are filled with NULL.

Cross Join: Returns the Cartesian product of both tables (every row from the first table joined with every row from the second).