SQL - JOIN

本例中，我们建立学生信息表（student_tab）和选课表（subject_tab）两张表。

学生信息表（student_tab）如下：

CREATE TABLE students_tbl(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    school VARCHAR(40) NOT NULL,
    PRIMARY KEY ( id )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;


+----+--------+--------------+
| id | name   | school       |
+----+--------+--------------+
|  1 | 小李   | 北京大学     |
|  2 | 小王   | 北京大学     |
|  3 | 小张   | 清华大学     |
|  4 | 小陈   | 清华大学     |
|  5 | 小刘   | 中山大学     |
+----+--------+--------------+

选课表（subject_tab）如下：

CREATE TABLE subject_tbl(
    id INT NOT NULL AUTO_INCREMENT,
    subject_name VARCHAR(100) NOT NULL,
    students_id INT NOT NULL,
    PRIMARY KEY ( id )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;


+----+--------------+-------------+
| id | subject_name | students_id |
+----+--------------+-------------+
|  1 | Python       |           1 |
|  2 | Java         |           1 |
|  3 | Python       |           3 |
|  4 | C++          |          10 |
+----+--------------+-------------+

---

引用两个表

我们可以通过引用两个表的方式，从两个表中获取数据，比如，现在我们要查询谁选了课，以及谁选了什么课程：

SELECT students_tbl.id, students_tbl.name, students_tbl.school, subject_tbl.subject_name
FROM students_tbl, subject_tbl
WHERE  students_tbl.id = subject_tbl.students_id;

+----+--------+--------------+--------------+
| id | name   | school       | subject_name |
+----+--------+--------------+--------------+
|  1 | 小李   | 北京大学     | Python        |
|  1 | 小李   | 北京大学     | Java          |
|  3 | 小张   | 清华大学     | Python        |
+----+--------+--------------+--------------+
3 rows in set (0.01 sec)

---

使用 JOIN

除了上面的方法，我们也可以使用关键词 JOIN 来从两个表中获取数据。

INNER JOIN

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

下例中，我们查询出所有选课的同学及其课程。

SELECT students_tbl.id, students_tbl.name, students_tbl.school, subject_tbl.subject_name
FROM students_tbl 
INNER JOIN subject_tbl
ON students_tbl.id = subject_tbl.students_id;

+----+--------+--------------+--------------+
| id | name   | school       | subject_name |
+----+--------+--------------+--------------+
|  1 | 小李   | 北京大学     | Python       |
|  1 | 小李   | 北京大学     | Java         |
|  3 | 小张   | 清华大学     | Python       |
+----+--------+--------------+--------------+

LEFT JOIN

LEFT JOIN 关键字会从左表 （students_tbl） 那里返回所有的行，即使在右表 （subject_tbl） 中没有匹配的行。

下例中，我们查询出所有学生，如有选课则显示，没有则为空。

SELECT students_tbl.id, students_tbl.name, students_tbl.school, subject_tbl.subject_name
FROM students_tbl 
LEFT JOIN subject_tbl
ON students_tbl.id = subject_tbl.students_id;

+----+--------+--------------+--------------+
| id | name   | school       | subject_name |
+----+--------+--------------+--------------+
|  1 | 小李   | 北京大学     | Python       |
|  1 | 小李   | 北京大学     | Java         |
|  3 | 小张   | 清华大学     | Python       |
|  2 | 小王   | 北京大学     | NULL         |
|  4 | 小陈   | 清华大学     | NULL         |
|  5 | 小刘   | 中山大学     | NULL         |
+----+--------+--------------+--------------+

RIGHT JOIN

RIGHT JOIN 关键字会右表 (subject_tbl) 那里返回所有的行，即使在左表 (students_tbl) 中没有匹配的行。

注释：在某些数据库中， RIGHT JOIN 称为 OUTER JOIN。

SELECT students_tbl.id, students_tbl.name, students_tbl.school, subject_tbl.subject_name
FROM students_tbl 
RIGHT JOIN subject_tbl
ON students_tbl.id = subject_tbl.students_id;

+------+--------+--------------+--------------+
| id   | name   | school       | subject_name |
+------+--------+--------------+--------------+
|    1 | 小李   | 北京大学     | Python       |
|    1 | 小李   | 北京大学     | Java         |
|    3 | 小张   | 清华大学     | Python       |
| NULL | NULL   | NULL         | C++          |
+------+--------+--------------+--------------+

FULL JOIN

只要其中某个表存在匹配，FULL JOIN 关键字就会返回行。

但由于 MySQL 不支持 FULL JOIN 功能，所有我们把一张 LEFT JOIN 的表和一张 RIGHT JOIN 的表用 UNION 关键字连接起来，达到 FULL JOIN 的效果。

SELECT students_tbl.id, students_tbl.name, students_tbl.school, subject_tbl.subject_name
FROM students_tbl 
LEFT JOIN subject_tbl
ON students_tbl.id = subject_tbl.students_id
UNION
SELECT students_tbl.id, students_tbl.name, students_tbl.school, subject_tbl.subject_name
FROM students_tbl 
RIGHT JOIN subject_tbl
ON students_tbl.id = subject_tbl.students_id;

+------+--------+--------------+--------------+
| id   | name   | school       | subject_name |
+------+--------+--------------+--------------+
|    1 | 小李   | 北京大学     | Python       |
|    1 | 小李   | 北京大学     | Java         |
|    3 | 小张   | 清华大学     | Python       |
|    2 | 小王   | 北京大学     | NULL         |
|    4 | 小陈   | 清华大学     | NULL         |
|    5 | 小刘   | 中山大学     | NULL         |
| NULL | NULL   | NULL         | C++          |
+------+--------+--------------+--------------+