Objectives
- Understand what a Right Join is.
- Learn how to use Right Joins to combine data from multiple tables.
- Practice using Right Joins.
What is a Right Join?
A Right Join is used to return all rows from the right table and the matched rows from the left table.
Syntax
SELECT *
FROM left_table
RIGHT JOIN right_table
ON left_table.column_name = right_table.column_name; Example
Consider the following tables:
left_table
| id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
right_table
| id | age |
|---|---|
| 1 | 25 |
| 2 | 27 |
When we use a right join, we will get all the rows from the right table and the matched rows from the left table.
SELECT *
FROM left_table
RIGHT JOIN right_table
ON left_table.id = right_table.id; output
| 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 left table?
Tables with missing data
Consider the following example. We don’t have a name for the person with id 2 in the left table.
left_table
| id | name |
|---|---|
| 1 | Alice |
right_table
| id | age |
|---|---|
| 1 | 25 |
| 2 | 27 |
We use the same right join query as before.
SELECT *
FROM left_table
RIGHT JOIN right_table
ON left_table.id = right_table.id; If there is no match in the left table, the columns from the left table will be NULL .
output
| id | name | age |
|---|---|---|
| 1 | Alice | 25 |
| 2 | NULL | 27 |
Exercise 3.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 Right Join.
fruits
| id | name |
|---|---|
| 1 | Apple |
| 2 | Banana |
| 3 | Orange |
prices
| id | price |
|---|---|
| 1 | 10 |
| 2 | 5 |
Interactive
Right Join
-
SELECT fruits.id, fruits.name, prices.price FROM fruits RIGHT JOIN prices ON fruits.id = prices.id;
Exercise 3.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 Right Join.
students
| student_id | name |
|---|---|
| 1 | Alia |
| 2 | Babu |
| 3 | Coco |
grades
| student_id | grade |
|---|---|
| 1 | O |
| 2 | A+ |
Interactive
Right Join
-
SELECT students.student_id, students.name, grades.grade FROM students RIGHT JOIN grades ON students.student_id = grades.student_id;
Summary
In this section, we learned about Right Joins and how to use them to combine data from multiple tables.