MySQL RIGHT JOIN

In the SQL language, the RIGHT JOIN command (also called RIGHT OUTER JOIN) is a type of join between 2 tables. It allows listing all the results of the right table even if there is no match in the second table.

 
 

 

Syntax:

To list the rows of table1, even if there is no match with table2, you need to perform an SQL query using the following syntax.

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.id = table2.fk_id

The query can also be written as follows:

SELECT *
FROM table1
RIGHT OUTER JOIN table2 ON table1.id = table2.fk_id

This syntax allows to list all rows in table2 (the right table) and display the associated data in table1 if there is a match between table1’s ID and table2’s FK_ID. If there is no match, the record of table2 will be displayed and the columns of table1 will all be NULL.
 

Example :

Let’s say we have an application that contains users and orders for each of these users. The database of this application contains a table for the users and saves their purchases in a second table. The 2 tables are linked thanks to the user_id column of the orders table. This allows you to associate an order with a user.
 
Users table:

> SELECT * FROM Users;

+------------+-----------+--------+-------------------------------+
|   UserID   |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     1      |    Alex   |   25   | 819 Saint Francis Way         |
|     2      |   Emily   |   15   | 171 Jarvisville Road Michigan |
|     3      |   Jean    |   35   | 188 Clay Street Indiana       |
|     4      |    Bob    |   40   | 285 Java Lane Missouri        |
+------------+-----------+--------+-------------------------------+

 

 
Orders table:

> SELECT * FROM Orders;

+---------+-----------+----------+-------------+
| OrderID |  NumOrder |   Total  |   UserID    |
+---------+-----------+----------+-------------+
|     1   |   9901    |  254.00  |      1      |
|     2   |   9902    |  300.96  |      1      |
|     3   |   9903    |  145.03  |      4      |
|     4   |   9904    |  744.33  |      5      |
+---------+-----------+----------+-------------+

To display all the orders with the name of the corresponding user it is normally done using INNER JOIN in SQL. Unfortunately, if the user has been deleted from the table, then it does not return the purchase. Using RIGHT JOIN allows you to return all the purchases and display the user’s name if it exists. To do this, you need to use this query:

SELECT UserID, Name, NumOrder, Total
FROM Users
RIGHT JOIN Orders ON Users.UserID = Orders.UserID

 
Output:

+---------+-----------+----------+-------------+
|  UserID |   Name    | NumOrder |    Total    |
+---------+-----------+----------+-------------+
|     1   |   Alex    |  9901    |  254.00     |
|     1   |   Alex    |  9902    |  300.96     |
|     4   |   Bob     |  9903    |  145.03     |
|   NULL  |   NULL    |  9904    |  744.33     |
+---------+-----------+----------+-------------+

This result shows that 9904 order is linked to user number 5. However, this user does not exist or no longer exists. Thanks to RIGHT JOIN, the purchase is still displayed but the information linked to the user is replaced by NULL.
 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More