In this tutorial, we will study the MySQL
RIGHT JOIN. A join is used to combine two or more tables using a commonly related column between them.
There are different types of joins in MySQL. They are –
In this article, we will focus on the right join, sometimes also referred to as the right outer join, and how to use the MySQL
RIGHT JOIN keyword to make a right join.
What is a Right Join?
Suppose you have two tables – table1 and table2. The
MySQL RIGHT JOIN keyword will return all the records of table2 and those records that matched from table1. If the records did not match from the left side, then NULL is returned as a value for those records. In other words, the right join includes the right table (table2) and the common records between table1 and table2. Let us see a diagrammatic representation of this.
Note that, some databases refer to the
RIGHT JOIN as
RIGHT OUTER JOIN.
Syntax of MySQL RIGHT JOIN
SELECT expression FROM table1 [t1] RIGHT JOIN table2 [t2] ON table1.column_name = table2.column_name;
Here ‘t1’ and ‘t2’ are optional aliases that you can have for the table names. The columns mentioned after the ON keyword are the related common columns using which the right join is to be performed.
In the above query, ‘table2’ is the rightmost table, which will be the focus of the right join.
Examples of MySQL RIGHT JOIN
Consider the below Students and Marks tables.
The Students table stores the details of the student in every row. The Marks table stores every student’s marks in English, Maths and Science out of 100.
Simple Example of MySQL RIGHT JOIN
Let us now make a right join on the Students table and Marks table such that we see every student’s ID, first name, last name, and their marks in Maths.
The first step is to find the commonly related columns between the two tables. In this case, they are ID from the Students table and StudentID from the Marks table. Let us now write a query for the right join using the
SELECT m.StudentID, s.FirstName, s.LastName, m.Maths FROM Students s RIGHT JOIN Marks m ON s.ID=m.StudentID;
In the above query, Marks is our rightmost table. We use ‘s’ as a shorthand notation to refer to the Students table and ‘m’ for the Marks table. We get the output as follows –
Wait, how does a student with the ID 9 have their first name and last name as NULL? As you can see the student with ID 9 does not have a record in the Students table. Since the right join includes all the records from the rightmost table, the marks in maths of the student with ID 9 are included in the result-set. However, since there is no corresponding data for the student with ID 9 in the Students table,
RIGHT JOIN returns NULL as their first name and last name.
Using MySQL RIGHT JOIN With Expressions
Building on the previous query, let’s make a right join on the Students table and Marks table such that we see every student’s ID, first name, last name, and percentage. To calculate the percentage of every student, we add their marks in the three subjects, divide it by 300 and then multiply that by 100. The query is –
SELECT m.StudentID, s.FirstName, s.LastName, ((m.English+m.Maths+m.Science)/300)*100 AS Percentage FROM Students s RIGHT JOIN Marks m ON s.ID=m.StudentID;
And we get that output as –
Using RIGHT JOIN With the WHERE Clause
Let us write a query which makes a right join on the Students and Marks table and displays the ID, First name, last name and marks in Maths of students if they have scored more than 40 in Maths. We will use the
WHERE clause. The query is –
SELECT m.StudentID, s.FirstName, s.LastName, m.Maths FROM Students s RIGHT JOIN Marks m ON s.ID=m.StudentID WHERE m.Maths>40;
And the output is –
Using RIGHT JOIN With the LIKE Clause
How about creating a right join on the Students and Marks table and displaying only those records where the student’s first name begins with ‘R’. We will use the
LIKE clause. The query is –
SELECT m.StudentID, s.FirstName, s.LastName, m.Maths FROM Students s RIGHT JOIN Marks m ON s.ID=m.StudentID WHERE s.FirstName LIKE 'R%';
And the output is –
RIGHT JOIN, also referred to in some databases as
RIGHT OUTER JOIN is an extremely important operation when it comes to MySQL. Joining two tables and displaying that data provides useful insights into the data. I would highly recommend you to practice some examples on MySQL