A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Assume we have two tables:

Employee

| emp_id | emp_name | dept_id |

| ------ | -------- | ------- |

| 1 | John | 1 |

| 2 | Alice | 2 |

| 3 | Bob | 2 |

| 4 | David | NULL |

Department

| dept_id | dept_name |

| ------- | --------- |

| 1 | HR |

| 2 | IT |

| 3 | Finance |

INNER JOIN

Returns only matching records from both tables.

Most commonly used

Excludes unmatched rows

SELECT e.emp_name, d.dept_name

FROM employee e

INNER JOIN department d

ON e.dept_id = d.dept_id;

Output

| emp_name | dept_name |

| -------- | --------- |

| John | HR |

| Alice | IT |

| Bob | IT |

LEFT JOIN (LEFT OUTER JOIN)

Returns all records from the left table, plus matching rows from the right.

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 |

| -------- | --------- |

| John | HR |

| Alice | IT |

| Bob | IT |

| David | NULL |

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all records from the right table, plus matching rows from the left.

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 |

| -------- | --------- |

| John | HR |

| Alice | IT |

| Bob | IT |

| NULL | Finance |

Not supported in all DBs (e.g., SQLite)

FULL JOIN (FULL OUTER JOIN)

Returns all records from both tables, matched or not.

SELECT e.emp_name, d.dept_name

FROM employee e

FULL OUTER JOIN department d

ON e.dept_id = d.dept_id;

Output

| emp_name | dept_name |

| -------- | --------- |

| John | HR |

| Alice | IT |

| Bob | IT |

| David | NULL |

| NULL | Finance |

❌ Not supported in MySQL (use UNION workaround)