Right Join (MySQL Join)
The RIGHT JOIN in MySQL is used to return all the rows from the right table and matching rows from the left table. If there is no match, NULL values are returned. In this tutorial, we'll discuss the syntax and usage of the RIGHT JOIN in MySQL.
Syntax
The basic syntax of the RIGHT JOIN in MySQL is:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Example
Consider the following two tables:
- Table A: users
| user_id | user_name | city |
|---|---|---|
| 1 | John | New York |
| 2 | Mary | Boston |
| 3 | Steve | Miami |
| 4 | Karen | Chicago |
- Table B: orders
| order_id | user_id | order_date |
|---|---|---|
| 1 | 1 | 2021-01-01 |
| 2 | 2 | 2021-02-02 |
| 3 | 1 | 2021-03-03 |
| 4 | 3 | 2021-04-04 |
We can use a RIGHT JOIN to join these tables on the user_id column:
SELECT *
FROM orders
RIGHT JOIN users ON orders.user_id = users.user_id;
The result of this query would be:
| order_id | user_id | order_date | user_id | user_name | city |
|---|---|---|---|---|---|
| 1 | 1 | 2021-01-01 | 1 | John | New York |
| 2 | 2 | 2021-02-02 | 2 | Mary | Boston |
| 3 | 1 | 2021-03-03 | 1 | John | New York |
| 4 | 3 | 2021-04-04 | null | null | null |
This shows that all the rows from the right table (orders) are included, along with any matching rows from the left table (users). If there is no match, NULL values are returned.
Explanation
In the above example, we used a RIGHT JOIN to join the two tables on the user_id column. This resulted in a new table that includes all the rows from the orders table, and only those rows from the users table where the user_id matched.
With a RIGHT JOIN, it's important to note that all the rows from the right table (orders) are included, even if there is no match in the left table (users). In such cases, NULL values are returned for columns from the left table.
Use
The RIGHT JOIN in MySQL is useful when you want to include all the rows from the right table, along with matching rows from the left table. This can be useful when you want to analyze data and include information from multiple tables.
Important Points
- The RIGHT JOIN in MySQL returns all rows from the right table and matching rows from the left table.
- If there is no match, NULL values are returned for the left table columns.
- In a RIGHT JOIN, all rows from the right table are included, and matching rows are returned from the left table.
Summary
In this tutorial, we discussed how to use a RIGHT JOIN in MySQL. We looked at the syntax and usage of the RIGHT JOIN, and we also went through an example to illustrate how it works. With this knowledge, you can now use the RIGHT JOIN in MySQL to join tables and analyze data that spans multiple tables.