MySQL RIGHT JOIN - MySQLCode

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 –

Inner Join
Left (Outer) Join
Right (Outer) Join
Full Outer Join

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;Code language: SQL (Structured Query Language) (sql)

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.

MySQL RIGHT JOIN Students Table — Students Table

MySQL RIGHT JOIN Marks Table — Marks Table

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 statement.

SELECT m.StudentID, s.FirstName, s.LastName, m.Maths FROM Students s 
RIGHT JOIN Marks m ON 
s.ID=m.StudentID;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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%';Code language: SQL (Structured Query Language) (sql)

And the output is –

Conclusion

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 RIGHT JOIN.