- Understand what a Left Join is.
- Learn how to use Left Joins to combine data from multiple tables.
- Practice using Left Joins.
What is a Left Join?
A Left Join is used to return all rows from the left table and the matched rows from the right table.
Syntax
SELECT *
FROM left_table
LEFT JOIN right_table
ON left_table.column_name = right_table.column_name; Example
Think of two tables, the left table and the right table.
Tables with matching data
| id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| id | age |
|---|---|
| 1 | 25 |
| 2 | 27 |
When we use a left join, we will get all the rows from the left table and the matched rows from the right table.
SELECT *
FROM left_table
LEFT JOIN right_table
ON left_table.id = right_table.id; | id | name | age |
|---|---|---|
| 1 | Alice | 25 |
| 2 | Bob | 27 |
In the above example, the left table has two rows, and the right table has two rows.
What would happen if there was missing data in the right table?
Tables with missing data
Consider the following example. We don’t have an age for the person with id 2 in the right table.
| id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| id | age |
|---|---|
| 1 | 25 |
We use the same left join query as before.
SELECT *
FROM left_table
LEFT JOIN right_table
ON left_table.id = right_table.id; If there is no match in the right table, the columns from the right table will be NULL .
| id | name | age |
|---|---|---|
| 1 | Alice | 25 |
| 2 | Bob | NULL |
Exercise 2.1
There are two tables fruits and prices . Fill in the blanks to complete the SQL statement to join the fruits and prices tables on the id column using a Left Join.
| id | name |
|---|---|
| 1 | Apple |
| 2 | Banana |
| 3 | Orange |
| id | price |
|---|---|
| 1 | 10 |
| 2 | 5 |
-
SELECT * FROM fruits LEFT JOIN prices ON fruits.id = prices.id;
Exercise 2.2
There are two tables students and grades . Fill in the blanks to complete the SQL statement to join the students and grades tables on the student_id column using a Left Join.
| student_id | name |
|---|---|
| 1 | Alia |
| 2 | Babu |
| 3 | Coco |
| student_id | grade |
|---|---|
| 1 | O |
| 2 | A+ |
-
SELECT students.student_id, students.name, grades.grade FROM students LEFT JOIN grades ON students.student_id = grades.student_id;
Summary
In this section, we learned about Left Joins and how to use them to combine data from multiple tables.