Question
What is a JOIN in SQL? List and explain the types of JOINs used in SQL with examples
A
Anonymous
December 15, 2025
55
Answer
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)
JuniorDatabase
Loading comments...