A2-02-15.DML-MySQL RIGHT JOIN
转载自：http://www.mysqltutorial.org/mysql-right-join/
MySQL RIGHT JOIN
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 reversed.
The following statement 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:
t1is the left table andt2is the right tablejoin_predicateis 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
t2table (right table) will appear at least once in the result set. - Based on the
join_predicate, if no matching row from thet1table (left table) exists,NULLwill appear in columns from thet1table for the rows that have no match in thet2table.
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;
|
The picture below illustrates the result of the RIGHT JOIN clause:
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;
|
Because we used RIGHT JOIN, all customers (right table) appears in the result set. We also found that some customers do not have dedicated sales rep indicated by NULL in the salesman column.
In this tutorial, you have learned how to use the MySQL RIGHT JOIN to query data from two or more tables.
