We’ll use two simple tables as reference:
Table: Employees
| EmpID | Name | DeptID |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 30 |
| 4 | David | NULL |
Table: Departments
| DeptID | DeptName |
|---|---|
| 10 | HR |
| 20 | IT |
| 30 | Finance |
| 40 | Marketing |
1. INNER JOIN
Returns only the matching rows between two tables.
SELECT e.Name, d.DeptName
FROM Employees e
INNER JOIN Departments d
ON e.DeptID = d.DeptID;
Result:
| Name | DeptName |
|---|---|
| Alice | HR |
| Bob | IT |
| Charlie | Finance |
(Only employees who have a matching department appear.)
2. LEFT OUTER JOIN
Returns all rows from the left table and matching rows from the right table. Unmatched rows in the right table are shown as NULL.
SELECT e.Name, d.DeptName
FROM Employees e
LEFT JOIN Departments d
ON e.DeptID = d.DeptID;
Result:
| Name | DeptName |
|---|---|
| Alice | HR |
| Bob | IT |
| Charlie | Finance |
| David | NULL |
3. RIGHT OUTER JOIN
Returns all rows from the right table and matching rows from the left table.
SELECT e.Name, d.DeptName
FROM Employees e
RIGHT JOIN Departments d
ON e.DeptID = d.DeptID;
Result:
| Name | DeptName |
|---|---|
| Alice | HR |
| Bob | IT |
| Charlie | Finance |
| NULL | Marketing |
4. FULL OUTER JOIN
Returns all rows from both tables, matching where possible, otherwise NULL.
SELECT e.Name, d.DeptName
FROM Employees e
FULL OUTER JOIN Departments d
ON e.DeptID = d.DeptID;
Result:
| Name | DeptName |
|---|---|
| Alice | HR |
| Bob | IT |
| Charlie | Finance |
| David | NULL |
| NULL | Marketing |
5. SELF JOIN
A table joined with itself. Useful for hierarchical data (e.g., employees & managers).
Suppose Employees also had a ManagerID:
| EmpID | Name | DeptID | ManagerID |
|---|---|---|---|
| 1 | Alice | 10 | NULL |
| 2 | Bob | 20 | 1 |
| 3 | Charlie | 30 | 2 |
| 4 | David | NULL | 2 |
Query:
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m
ON e.ManagerID = m.EmpID;
Result:
| Employee | Manager |
|---|---|
| Alice | NULL |
| Bob | Alice |
| Charlie | Bob |
| David | Bob |
6. NATURAL JOIN
Automatically joins tables based on columns with the same name (be cautious, can cause errors if column names clash).
SELECT *
FROM Employees
NATURAL JOIN Departments;
(Since both have DeptID, it will join on that.)
Result:
| EmpID | Name | DeptID | DeptName |
|---|---|---|---|
| 1 | Alice | 10 | HR |
| 2 | Bob | 20 | IT |
| 3 | Charlie | 30 | Finance |
7. CROSS JOIN
Cartesian product — returns all possible combinations of rows.
SELECT e.Name, d.DeptName
FROM Employees e
CROSS JOIN Departments d;
Result (4 employees × 4 departments = 16 rows):
| Name | DeptName |
|---|---|
| Alice | HR |
| Alice | IT |
| Alice | Finance |
| Alice | Marketing |
| Bob | HR |
| Bob | IT |
| … | … |