概述
连接从类型上来说有两种连接, 一种是内连接(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求自然连接, 只存在于左边和只存在于右边的并集