MariaDB右外連接

MariaDB RIGHT OUTER JOIN用于返回ON條件中指定的右表中的所有行，并且僅返回來自其他表中連接字段滿足條件的行。

MariaDB RIGHT OUTER JOIN也被稱為RIGHT JOIN。

語法：

SELECT columns  
FROM table1  
RIGHT [OUTER] JOIN table2  
ON table1.column = table2.column;

圖形表示如下：

注： 上圖中，兩個圖形的左側表(table1)和右側表(table2)中間交叉藍色部分，以及右側表(table2)就是連接返回的結果集。

為了方便演示，我們需要創(chuàng)建兩個表，并插入一些數(shù)據(jù) -

USE testdb;
DROP table if exists students;
DROP table if exists subjects;
DROP table if exists scores;
-- 學生信息
CREATE TABLE students(  
    student_id INT NOT NULL AUTO_INCREMENT,  
    student_name VARCHAR(100) NOT NULL,  
    student_address VARCHAR(40) NOT NULL,  
    admission_date DATE,  
    PRIMARY KEY ( student_id )
);

-- 科目信息
CREATE TABLE subjects(  
    subject_id INT NOT NULL AUTO_INCREMENT,  
    subject_name VARCHAR(100) NOT NULL,
    PRIMARY KEY ( subject_id )
);

-- 成績信息
CREATE TABLE scores(  
    id INT NOT NULL AUTO_INCREMENT,
    student_id int(10) NOT NULL,
    subject_id int(10) NOT NULL,
    score float(4,1) DEFAULT NULL,
    created_time datetime DEFAULT NULL,
    PRIMARY KEY ( id )
);

插入數(shù)據(jù) -

--- 學生信息數(shù)據(jù)
INSERT INTO students  
(student_id, student_name, student_address, admission_date)  
VALUES(1,'Maxsu','Haikou','2017-01-07 00:00:00');
INSERT INTO students  
(student_id, student_name, student_address, admission_date)  
VALUES  
(2,'JMaster','Beijing','2016-05-07 00:00:00'),  
(3,'Mahesh','Guangzhou','2016-06-07 00:00:00'),  
(4,'Kobe','Shanghai','2016-02-07 00:00:00'),  
(5,'Blaba','Shenzhen','2016-08-07 00:00:00');

-- 科目信息數(shù)據(jù)
INSERT INTO subjects  
(subject_id, subject_name)  
VALUES(1,'計算機網絡基礎');

INSERT INTO subjects  
(subject_id, subject_name)  
VALUES(2,'高等數(shù)學');

INSERT INTO subjects  
(subject_id, subject_name)  
VALUES(3,'離散數(shù)學');

-- 分數(shù)
INSERT INTO scores  
(student_id, subject_id, score, created_time)  
VALUES(1,1,81,'2017-11-18 19:30:02');

INSERT INTO scores  
(student_id, subject_id, score, created_time)  
VALUES(1,2,89,NOW());

INSERT INTO scores  
(student_id, subject_id, score, created_time)  
VALUES(1,3,92,NOW());

INSERT INTO scores  
(student_id, subject_id, score, created_time)  
VALUES(2,2,95,NOW());

INSERT INTO scores  
(student_id, subject_id, score, created_time)  
VALUES(2,3,72,NOW());

INSERT INTO scores  
(student_id, subject_id, score, created_time)  
VALUES(3,1,59,NOW());

INSERT INTO scores  
(student_id, subject_id, score, created_time)  
VALUES(3,3,77,NOW());

INSERT INTO scores  
(student_id, subject_id, score, created_time)  
VALUES(4,2,81,NOW());

當前studens表中的行記錄如下 -

MariaDB [testdb]> select * from students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
|          1 | Maxsu        | Haikou          | 2017-01-07     |
|          2 | JMaster      | Beijing         | 2016-05-07     |
|          3 | Mahesh       | Guangzhou       | 2016-06-07     |
|          4 | Kobe         | Shanghai        | 2016-02-07     |
|          5 | Blaba        | Shenzhen        | 2016-08-07     |
+------------+--------------+-----------------+----------------+
5 rows in set (0.00 sec)

當前score表中的行記錄如下 -

MariaDB [testdb]> select * from scores;
+----+------------+------------+-------+---------------------+
| id | student_id | subject_id | score | created_time        |
+----+------------+------------+-------+---------------------+
|  1 |          1 |          1 |  81.0 | 2017-11-18 19:30:02 |
|  2 |          1 |          2 |  89.0 | 2017-11-28 22:31:57 |
|  3 |          1 |          3 |  92.0 | 2017-11-28 22:31:58 |
|  4 |          2 |          2 |  95.0 | 2017-11-28 22:31:58 |
|  5 |          2 |          3 |  72.0 | 2017-11-28 22:31:58 |
|  6 |          3 |          1 |  59.0 | 2017-11-28 22:31:58 |
|  7 |          3 |          3 |  77.0 | 2017-11-28 22:31:58 |
|  8 |          4 |          2 |  81.0 | 2017-11-28 22:31:58 |
+----+------------+------------+-------+---------------------+
8 rows in set (0.00 sec)

示例1

使用以下語法根據(jù)給定的條件連接兩個表 - students和scores，即查詢學生信息和對應的成績信息，如果沒有成績則使用NULL值表示。

SELECT scores.subject_id, scores.score,students.student_id, students.student_name
FROM scores
RIGHT JOIN students
ON students.student_id = scores.student_id
ORDER BY students.student_id;

上面查詢語句查詢所有科目的考試分數(shù)以及學生，得到以下結果 -

MariaDB [testdb]> SELECT scores.subject_id, scores.score,students.student_id, students.student_name
    -> FROM scores
    -> RIGHT JOIN students
    -> ON students.student_id = scores.student_id
    -> ORDER BY students.student_id;
+------------+-------+------------+--------------+
| subject_id | score | student_id | student_name |
+------------+-------+------------+--------------+
|          1 |  81.0 |          1 | Maxsu        |
|          2 |  89.0 |          1 | Maxsu        |
|          3 |  92.0 |          1 | Maxsu        |
|          2 |  95.0 |          2 | JMaster      |
|          3 |  72.0 |          2 | JMaster      |
|          1 |  59.0 |          3 | Mahesh       |
|          3 |  77.0 |          3 | Mahesh       |
|          2 |  81.0 |          4 | Kobe         |
|       NULL |  NULL |          5 | Blaba        |
+------------+-------+------------+--------------+
9 rows in set (0.00 sec)

上面示例的查詢結果中，由于最后一行(student_id=5)的學生還沒有任何分數(shù)信息，所以在使用RIGHT JOIN連接后，左側表(scores)相關列的值使用NULL來填充?？梢钥吹接覀缺?students)的每一行都有列出來了。

示例2

查詢指定學生，并且成績大于85分的信息 -

SELECT students.student_id, students.student_name, scores.subject_id, scores.score  
FROM scores
RIGHT JOIN students
ON students.student_id = scores.student_id
WHERE students.student_name='Maxsu' AND scores.score > 85;

執(zhí)行上面查詢語句，得到以下結果 -

MariaDB [testdb]> SELECT students.student_id, students.student_name, scores.subject_id, scores.score
    -> FROM scores
    -> RIGHT JOIN students
    -> ON students.student_id = scores.student_id
    -> WHERE students.student_name='Maxsu' AND scores.score > 85;
+------------+--------------+------------+-------+
| student_id | student_name | subject_id | score |
+------------+--------------+------------+-------+
|          1 | Maxsu        |          2 |  89.0 |
|          1 | Maxsu        |          3 |  92.0 |
+------------+--------------+------------+-------+
2 rows in set (0.00 sec)

示例3

查詢沒有考試成績的學生信息(尚未錄入) -

SELECT students.student_id, students.student_name, scores.subject_id, scores.score  
FROM scores
RIGHT JOIN students
ON students.student_id = scores.student_id
WHERE scores.score IS NULL;

執(zhí)行上面查詢語句，得到以下結果 -

MariaDB [testdb]> SELECT students.student_id, students.student_name, scores.subject_id, scores.score
    -> FROM scores
    -> RIGHT JOIN students
    -> ON students.student_id = scores.student_id
    -> WHERE scores.score IS NULL;
+------------+--------------+------------+-------+
| student_id | student_name | subject_id | score |
+------------+--------------+------------+-------+
|          5 | Blaba        |       NULL |  NULL |
+------------+--------------+------------+-------+
1 row in set (0.00 sec)