Objectives
- Understand what a Full Join is.
- Learn how to use Full Joins to combine data from multiple tables.
- Practice using Full Joins.
What is a Full Join?
A Full Join is used to return rows when there is a match in one of the tables. Even if there’s no match in the other table, those rows are still included, with NULL values for the missing columns.
Syntax
SELECT *
FROM left_table
FULL JOIN right_table
ON left_table.column_name = right_table.column_name; Example
Imagine two tables about fruit data:
fruits
| id | name | category_id |
|---|---|---|
| 1 | Apple | 1 |
| 2 | Banana | 2 |
| 4 | Grape | NULL |
sales
| id | fruit_name | quantity_sold |
|---|---|---|
| 1 | Apple | 120 |
| 2 | Banana | 85 |
| 3 | Mango | 60 |
-
The FULL JOIN captures all fruits from the fruits table and all sales records from the sales table.
-
Apple and Banana correctly match with their sales quantities.
-
Grape appears even though it hasn’t sold any units yet (NULL).
-
Mango’s sales are included, even though it’s not in our current fruit inventory (NULL for name in the fruits table).
A Full Join helps us see the whole picture:
output
| name | quantity_sold |
|---|---|
| Apple | 120 |
| Banana | 85 |
| Grape | NULL |
| Mango | 60 |
Exercise 4.1
Write a SQL statement to perform a Full Join on the fruits and sales tables.
fruits
| id | name | category_id |
|---|---|---|
| 1 | Apple | 1 |
| 2 | Banana | 2 |
| 4 | Grape | NULL |
sales
| id | fruit_name | quantity_sold |
|---|---|---|
| 1 | Apple | 120 |
| 2 | Banana | 85 |
| 3 | Mango | 60 |
Interactive
Full Join
-
SELECT fruits.name, sales.quantity_sold FROM fruits FULL JOIN sales ON fruits.name = sales.fruit_name;