What is MySQL RIGHT JOIN and how can we write MySQL query for it?
Answers
Summary: in this tutorial, you will learn how to use the MySQL RIGHT JOIN to query data from two or more tables.
Introduction to MySQL RIGHT JOIN clause
MySQL RIGHT JOIN is similar to LEFT JOIN, except the treatment of table reverexamplnt queries data from two tables t1 and t2 using the RIGHT JOIN clause:
1
2
3
4
SELECT
*
FROM t1
RIGHT JOIN t2 ON join_predicate;
In this statement:
t1 is the left table and t2 is the right table
join_predicate is the condition to match rows on the left table (t1) with rows on the right table (t2)
The join_predicate could be in the following form:
1
t1.pk = t2.fk
or if the common columns of the two table have the same name, you can use the following syntax:
1
USING (common_column);
The following describes how the RIGHT JOIN clause works.
All rows from the t2 table (right table) will appear at least once in the result set.
Based on the join_predicate, if no matching row from the t1 table (left table) exists, NULL will appear in columns from the t1 table for the rows that have no match in the t2 table.
It is important to emphasize that RIGHT JOIN and LEFT JOIN clauses are functionally equivalent and they can replace each other as long as the table order is switched.
Note that the RIGHT OUTER JOIN is a synonym for RIGHT JOIN.
MySQL RIGHT JOIN example
Suppose we have two tables t1 and t2 with the following structures and data:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE t1 (
id INT PRIMARY KEY,
pattern VARCHAR(50) NOT NULL
);
CREATE TABLE t2 (
id VARCHAR(50) PRIMARY KEY,
pattern VARCHAR(50) NOT NULL
);
INSERT INTO t1(id, pattern)
VALUES(1,'Divot'),
(2,'Brick'),
(3,'Grid');
INSERT INTO t2(id, pattern)
VALUES('A','Brick'),
('B','Grid'),
('C','Diamond');
The following query joins two tables t1 and t2 using the pattern column:
1
2
3
4
5
6
SELECT
t1.id, t2.id
FROM
t1
RIGHT JOIN t2 USING (pattern)
ORDER BY t2.id;
MySQL RIGHT JOIN result
The picture below illustrates the result of the RIGHT JOIN clause:
MySQL RIGHT JOIN
See the following employees and customers in the sample database.
The following query get the sales representatives and their customers:
1
2
3
4
5
6
7
8
9
10
SELECT
concat(e.firstName,' ', e.lastName) salesman,
e.jobTitle,
customerName
FROM
employees e
RIGHT JOIN
customers c ON e.employeeNumber = c.salesRepEmployeeNumber
AND e.jobTitle = 'Sales Rep'
ORDER BY customerName;
MySQL RIGHT JOIN example
I hope this will help you.