MySQL连表查询

　　参考：https://blog.csdn.net/lj1994104/article/details/79671807

　　一，MySQL连接查询类型

　　A）内连接：join，inner join

　　B）外连接：left join，left outer join，right join，right outer join，union

　　C）交叉连接：cross join

　　二，下面以示例进行分析

　　创建两张表，把表当成一个集合那么表中的元素就是集合的一个元素

　　创建a表

?

1 2 3 4 5

CREATE TABLE `a` (   `id` int(10) NOT NULL AUTO_INCREMENT,   `age` int(2) DEFAULT NULL,   PRIMARY KEY (`id`) ) ;

 　　创建b表

?

1 2 3 4 5

CREATE TABLE `b` (   `id` int(10) NOT NULL AUTO_INCREMENT,   `name` varchar(32) DEFAULT NULL,   PRIMARY KEY (`id`) ) ;

 　　插入测试数据

       

 

 

 　　2.1内连接inner join或者join(等同与inner join)

?

1 2 3 4 5 6 7 8

mysql> select * from a inner join b on a.id=b.id; +----+------+----+-------+ | id | age  | id | name  | +----+------+----+-------+ |  2 |   11 |  2 | zhang | |  3 |   12 |  3 | li    | |  4 |   13 |  4 | zhou  | +----+------+----+-------+

 　　应用场景

 

 　　这种场景下得到的是满足某一条件的A，B内部的数据；本次需要满足的条件是a.id等于b。id

　　正因为得到的是内部共有数据，所以连接方式称为内连接。

　　2.2外连接(6种场景)

　　2.2.1 left join 或者left outer join(等同于left join)

?

1 2 3 4 5 6 7 8 9

mysql> select * from a left join b on a.id=b.id; +----+------+------+-------+ | id | age  | id   | name  | +----+------+------+-------+ |  2 |   11 |    2 | zhang | |  3 |   12 |    3 | li    | |  4 |   13 |    4 | zhou  | |  1 |   10 | NULL | NULL  | +----+------+------+-------+

 　　Table B中不存在的记录填充NULL

　　应用场景

 

 

　　2.2.2  [left   join 或者left outer join(等同于left join)]  +  [where B.column is null] 

?

1 2 3 4 5 6

mysql> select * from a left join b on a.id=b.id where b.id is null; +----+------+------+------+ | id | age  | id   | name | +----+------+------+------+ |  1 |   10 | NULL | NULL | +----+------+------+------+

 　　应用场景

 

 　　

　　这种场景下得到的是A中的所有数据减去"与B满足同一条件 的数据"，然后得到的A剩余数据；

　　2.2.3  right join 或者fight outer join(等同于right join)

?

1 2 3 4 5 6 7 8 9

mysql> select * from a right join b on a.id=b.id; +------+------+----+-------+ | id   | age  | id | name  | +------+------+----+-------+ |    2 |   11 |  2 | zhang | |    3 |   12 |  3 | li    | |    4 |   13 |  4 | zhou  | | NULL | NULL |  5 | chen  | +------+------+----+-------+

 　　应用场景

 

 　　这种场景下得到的是B的所有数据，和满足某一条件的A的数据；

　　2.2.4 [left   join 或者left outer join(等同于left join)]  +  [where A.column is null]

?

1 2 3 4 5 6

mysql> select * from a right join b on a.id=b.id where a.id is null; +------+------+----+------+ | id   | age  | id | name | +------+------+----+------+ | NULL | NULL |  5 | chen | +------+------+----+------+

 　　应用场景

　　

 

 　　这种场景下得到的是B中的所有数据减去 "与A满足同一条件 的数据“，然后得到的B剩余数据；

　　2.2.5 full join （mysql不支持，但是可以用 left join  union right join代替）

?

1 2 3 4 5 6 7 8 9 10

mysql> select * from a left join b on a.id=b.id union select * from a right join b on a.id=b.id; +------+------+------+-------+ | id   | age  | id   | name  | +------+------+------+-------+ |    2 |   11 |    2 | zhang | |    3 |   12 |    3 | li    | |    4 |   13 |    4 | zhou  | |    1 |   10 | NULL | NULL  | | NULL | NULL |    5 | chen  | +------+------+------+-------+

 　　union过后，重复的记录会合并（id为2，3，4的三条记录），

　　应用场景

 

 　　这种场景下得到的是满足某一条件的公共记录，和独有的记录

　　

　　2.2.6 full join + is null（mysql不支持，但是可以用 （left join + is null） union （right join+isnull代替）

?

1 2 3 4 5 6 7

mysql> select * from a left join b on a.id=b.id where b.id is null union select * from a right join b on a.id=b.id where a.id is null; +------+------+------+------+ | id   | age  | id   | name | +------+------+------+------+ |    1 |   10 | NULL | NULL | | NULL | NULL |    5 | chen | +------+------+------+------+

 　　应用场景

 

 　　

　　这种场景下得到的是A，B中不满足某一条件的记录之和

　　注:上面共有其中七（2^3-1）种应用场景，还有一种是全空白，那就是什么都不查，七种情形包含了实际应用所有可能的场景

　　2.3交叉连接(cross join)

　　2.3.1 实际应用中还有这样一种情形，想得到A，B记录的排列组合，即笛卡儿积，这个就不好用集合和元素来表示了。需要用到cross join：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

mysql> select * from a cross join b;                                                                           +----+------+----+-------+ | id | age  | id | name  | +----+------+----+-------+ |  1 |   10 |  2 | zhang | |  2 |   11 |  2 | zhang | |  3 |   12 |  2 | zhang | |  4 |   13 |  2 | zhang | |  1 |   10 |  3 | li    | |  2 |   11 |  3 | li    | |  3 |   12 |  3 | li    | |  4 |   13 |  3 | li    | |  1 |   10 |  4 | zhou  | |  2 |   11 |  4 | zhou  | |  3 |   12 |  4 | zhou  | |  4 |   13 |  4 | zhou  | |  1 |   10 |  5 | chen  | |  2 |   11 |  5 | chen  | |  3 |   12 |  5 | chen  | |  4 |   13 |  5 | chen  | +----+------+----+-------+

 　　

　　2.3.2 还可以为cross  join指定条件 （where）：

?

1 2 3 4 5 6 7 8

mysql> select * from a cross join b where a.id=b.id; +----+------+----+-------+ | id | age  | id | name  | +----+------+----+-------+ |  2 |   11 |  2 | zhang | |  3 |   12 |  3 | li    | |  4 |   13 |  4 | zhou  | +----+------+----+-------+

 　　注：这种情况实际上实现了内连接

　　

　　三 注意事项

　　上面仍然存在遗漏，那就是mysql对sql语句的容错问题，即在sql语句不完全符合书写建议的情况，mysql会允许这种情况，尽可能地解释它:

　　3.1 一般cross join后面加上where条件，但是用cross join+on也是被解释为cross join+where；

　　3.2 一般内连接都需要加上on限定条件，如上面场景2.1；如果不加会被解释为交叉连接；

　　3.3 如果连接表格使用的是逗号，会被解释为交叉连接；

　　注:sql标准中还有union join和natural  inner join，mysql不支持，而且本身也没有多大意义，其结果可以用上面的几种连接方式得到