mysql表的连接

概述

连接从类型上来说有两种连接, 一种是内连接(inner join), 另一种是外连接(outer join)

内连接就是只连接同时存在与两个表中的内容,忽略掉所有不匹配的信息, 比如说自然连接就是内连接, 只是省略了inner关键字

natural join是将两个表中相同属性的值做比较, 如果相同则放在一起, 如果, 两个表中有多个相同的属性, 那么则要求两个属性的值都相同才能链接到一起, 如果只想通过一个属性join,请使用A join B using(culum)

假如A表与B表有共同的属性ID, 自然链接可以使用以下三种写法:

select * from A natural join B;
select * from A, B where A.ID = B.ID;
select * from A join B using(ID);

外连接就是连接两个表中所有的内容, 对于没有其中一端匹配的记录, 使用NULL元组补齐显示

因为连接针对是两个表的,所以外连接就分成两种情况, 一种是左外连接, 或者叫左连接, 另外一种叫右外连接, 又叫右连接

左外连接就是当两个表进行连接时, 对于存在于左边表中的元组, 如果右边表中无法找到与之匹配的信息, 那么就使用NULL代替显示的右边的信息

反之, 右连接就是当两个表进行连接时, 对于存在于右边表中的元组, 如果左边表中无法找到与之匹配的信息, 那么就使用NULL代替显示的左边边的信息

简而言之, 就是是什么链接就保留哪一边表中的原始信息, 而用NULL代替另外一边的信息

注意: MySQL中没有全连接

以下使用两个表来看看连接的效果是什么样的

person表, 含有每个人的ID和姓名

mysql> select * from person;
+------+---------+
| id   | name    |
+------+---------+
| 111  | xanarry |
| 111  | xanarry |
| 222  | marry   |
| 333  | bob     |
| 444  | carly   |
| 555  | tom     |
| 666  | jerry   |
+------+---------+
7 rows in set (0.00 sec)

salary表, 含有每个ID对应的工资money

mysql> select * from salary;
+------+-------+
| id   | money |
+------+-------+
| 444  |  1232 |
| 555  |  3213 |
| 666  |  4231 |
| 777  |  2552 |
| 888  |  6742 |
| 999  |  8724 |
+------+-------+
6 rows in set (0.00 sec)

这是自然连接的效果, 可以看出, 任意表中与另外一个表不匹配的信息都被过滤掉了, 使用inner与不使用输出结果是一致的

mysql> select * from person join salary using(id);
+------+-------+-------+
| id   | name  | money |
+------+-------+-------+
| 444  | carly |  1232 |
| 555  | tom   |  3213 |
| 666  | jerry |  4231 |
+------+-------+-------+
3 rows in set (0.01 sec)

使用inner关键词

mysql> select * from person inner join salary using(id);
+------+-------+-------+
| id   | name  | money |
+------+-------+-------+
| 444  | carly |  1232 |
| 555  | tom   |  3213 |
| 666  | jerry |  4231 |
+------+-------+-------+
3 rows in set (0.00 sec)

左链接, 同样, 在使用左右连接是是否使用outer关键词都能得到一样的效果

可以看出输出了左边表中id为111,222,333的内容, 但是右边表中没有内容与这几项匹配, 所以使用NULL代替输出

mysql> select * from person left join salary using(id);
+------+---------+-------+
| id   | name    | money |
+------+---------+-------+
| 444  | carly   |  1232 |
| 555  | tom     |  3213 |
| 666  | jerry   |  4231 |
| 111  | xanarry |  NULL |
| 111  | xanarry |  NULL |
| 222  | marry   |  NULL |
| 333  | bob     |  NULL |
+------+---------+-------+
7 rows in set (0.00 sec)

mysql> select * from person left outer join salary using(id);
+------+---------+-------+
| id   | name    | money |
+------+---------+-------+
| 444  | carly   |  1232 |
| 555  | tom     |  3213 |
| 666  | jerry   |  4231 |
| 111  | xanarry |  NULL |
| 111  | xanarry |  NULL |
| 222  | marry   |  NULL |
| 333  | bob     |  NULL |
+------+---------+-------+
7 rows in set (0.00 sec)

右链接, 同样, 在使用左右连接是是否使用outer关键词都能得到一样的效果

可以看出输出了右边边表中id为777,888,999的内容, 但是左边边表中没有内容与这几项匹配, 所以使用NULL代替输出

mysql> select * from person right join salary using(id);
+------+-------+-------+
| id   | money | name  |
+------+-------+-------+
| 444  |  1232 | carly |
| 555  |  3213 | tom   |
| 666  |  4231 | jerry |
| 777  |  2552 | NULL  |
| 888  |  6742 | NULL  |
| 999  |  8724 | NULL  |
+------+-------+-------+
6 rows in set (0.00 sec)

使用嵌套子查询实现join查询的方法

实现左连接的方法:

1.先使用自然连接;

2.然后再通过嵌套子查询将左表中不存在与右表的元组输出, 列数不够的用NULL补齐

3.输出上面两个查询的交集(union)

例子:
假如A表有3列, B表有三列, 有共同列ID, 那么两个表的左连接为

select * from A natural left join B;

使用子查询实现

select * from A natural join B
union
select A.1列, A.2列, A.3列, NULL, NULL from A
where A.id 
not in
(select ID from B);

同样右连接只需要交换顺序即可

全连接的则使用两个union求自然连接, 只存在于左边和只存在于右边的并集