SQL RIGHT JOIN

SQL RIGHT JOIN statement returns all the records present in the right table and matched records from the left table. However, the result will contain null for the rows which are not present in the left table. In this article, we will discuss the SQL RIGHT JOIN Statement. Also, we will discuss a few examples of writing queries.

Note: RIGHT JOIN is also known as RIGHT OUTER JOIN.

Syntax

SELECT table1.column1, table2.column2 … FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;

• table1.column1: The columns which you need to fetch from table1.
• table2.column2: The columns which you want to fetch from table2.
• table1: The name of the first table.
• table2: The name of the second table.
• table1.common_field = table2.common_field: The Join Condition.

Also, you can use SQL Aliases in order to give a temporary name to table1 and table2. This can help in using short memorable names for the tables.

SELECT t1.column1, t2.column2 … FROM table1 AS t1 RIGHT JOIN table2 AS t2 ON t1.common_field = t2.common_field;

Demo Tables

Refer the following tables for all the examples in this article. We have three tables: users, orders and user_description.

mysql> SELECT * FROM users;
+---------+----------+------------------+
| user_id | username | email_id         |
+---------+----------+------------------+
|       1 | john     | [email protected]   |
|       2 | emily    | [email protected]  |
|       3 | monica   | [email protected] |
|       4 | clark    | [email protected]  |
+---------+----------+------------------+

mysql> SELECT * FROM orders;
+----------+---------+-------------+------------+
| order_id | user_id | order_total | order_date |
+----------+---------+-------------+------------+
|        1 |       1 |         500 | 2019-07-01 |
|        2 |       1 |         400 | 2019-06-01 |
|        3 |       4 |        1000 | 2018-07-15 |
|        5 |       3 |         600 | 2019-06-05 |
|        6 |      10 |        1000 | 2019-06-05 |
+----------+---------+-------------+------------+

Example

For instance, we want to fetch all the order details from the table and include their user details (if they are available). If we wanted to fetch only those orders for which we have user details, then we should use SQL INNER JOIN.

SELECT u.user_id, u.email_id, o.order_id, o.order_total 
FROM users AS u 
RIGHT JOIN orders AS o ON u.user_id = o.user_id;
+---------+------------------+----------+-------------+
| user_id | email_id         | order_id | order_total |
+---------+------------------+----------+-------------+
|       1 | [email protected]   |        1 |         500 |
|       1 | [email protected]   |        2 |         400 |
|       4 | [email protected]  |        3 |        1000 |
|       3 | [email protected] |        5 |         600 |
|    null | null             |        6 |        1000 |
+---------+------------------+----------+-------------+

Since order_id 6 does not have the corresponding user information, the user_id and email_id corresponding to it is null.

Conclusion

In conclusion, we discussed the SQL RIGHT JOIN. You can read more about it on Wikipedia. Additionally, you can also learn about SQL on Concatly.