# 连接查询

现在有两个表，其结构及数据如下：

classes 表

students 表

# 1. 左连接 (LEFT OUTER JOIN)

以左表为主，返回左表都存在的行，如果右表没有对应的数据，则显示为NULL。

SELECT * FROM classes c
LEFT JOIN students s
ON c.`id` = s.`class_id`

结果：

id  name        id  class_id  name    gender  

 1  一班           1  1         小明      M       
 1  一班           2  1         小红      F       
 1  一班           3  1         小军      M       
 1  一班           4  1         小米      F       
 2  二班           5  2         小白      F       
 2  二班           6  2         小兵      M       
 2  二班           7  2         小林      M       
 3  三班           8  3         小新      F       
 3  三班           9  3         小王      M       
 3  三班          10  3         小丽      F       
 4  四班      (NULL)  (NULL)    (NULL)  (NULL)  

图示：

# 左连接的坑:

现在有个需求：找出每个班级的名称及其对应的女同学数量。

脑子的 sql 大概是这样的：

SELECT c.name, count(s.name) as num 
    FROM classes c left join students s 
    on s.class_id = c.id 
    where s.gender = 'F'
    group by c.name

然后结果为：

name       num  

一班             2

三班             2

二班             1

四班 没了。结果明显不正确。正确的 sql 如下：

SELECT c.name, COUNT(s.name) AS num 
    FROM classes c LEFT JOIN students s 
    ON s.class_id = c.id AND s.gender = 'F'
    GROUP BY c.name

正确结果：

name       num  

一班             2

三班             2

二班             1

四班             0

其区别是条件 s.gender = 'F'位置不同而已。针对左连接来说，ON中的条件限制右表(左表的数据都在)，where中的条件限制左表(左表的数据会被过滤)。

# 2.右连接 (RIGHT OUTER JOIN)

同理，以右表为主，返回右表都存在的行，如果左表没有对应的数据，则显示为NULL。

SELECT * FROM classes c
RIGHT JOIN students s
ON c.`id` = s.`class_id`

结果：

id  name        id  class_id  name    gender  

1 一班 1 1 小明 M
1 一班 2 1 小红 F
1 一班 3 1 小军 M
1 一班 4 1 小米 F
2 二班 5 2 小白 F
2 二班 6 2 小兵 M
2 二班 7 2 小林 M
3 三班 8 3 小新 F
3 三班 9 3 小王 M
3 三班 10 3 小丽 F

图示：

# 3.内连接 (INNER JOIN)

只返回同时存在于两张表的行数据。排除掉NULL的值。等同于直接 where 条件连接

SELECT * FROM classes c
INNER JOIN students s
ON c.`id` = s.`class_id`

结果：

id  name        id  class_id  name    gender  

 1  一班           1  1         小明      M       
 1  一班           2  1         小红      F       
 1  一班           3  1         小军      M       
 1  一班           4  1         小米      F       
 2  二班           5  2         小白      F       
 2  二班           6  2         小兵      M       
 2  二班           7  2         小林      M       
 3  三班           8  3         小新      F       
 3  三班           9  3         小王      M       
 3  三班          10  3         小丽      F       

图示：

# 4.全外连接 (FULL OUTER JOIN)

把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL。但是 Mysql 不支持，可以使用 左连接 + union(去重复) + 右连接 代替，数据多的时候性能差。

SELECT *
FROM students s
FULL OUTER JOIN classes c
ON s.`class_id`=c.`id`

图示：