【数据库】mysql多表查询的四种视图联接方式 - 公牛博客

需求场景:

数据库中，我们一般不会使所有数据都放在一个表中，但查询时可以同时关联查询多个表从而输出我们想要看到的数据。

是说同时从多个表中获取我们想要的数据展现在视图中，但在查询时存在一定限制，多个表之间一般需要一个关联的列。

查询中并不是所有行都能满足关联条件，自然查询不匹配就会重复或遗漏显示数据，所以sql给出多种查询视图联接类型。

这种联接条件可在from或where子句中指定，大致分为三类联接方式：内联接、外联接、交叉联接；

其中内联接一种典型的联接运算，使用=或<>之类的比较运算符号，包含相等联接和自然联接。

就拿左右两个表来说，内部联接，不论是左表还是右表，只要其在另一个表中没有匹配的值，则此行均不显示；

外联接有三种视图类型：左向外联接、右向外联接、完整外部联接；

其中左向外联接、关键字left join 或left outer join，意思是显示左表所有数据，如果其在右表中没有匹配的值则视图结果右表的值为空“null”；而右表的行如果在左表中没有匹配的值则不显示；

同理右向外联接、关键字right join或right outer join，意思是显示右表所有数据，如果其在左表中没有匹配的值则视图结果左表的值为null；而左表的行如果对应匹配的值则不显示；

而完全外部联接、关键字full join或full outer join，意思是查询视图返回两表所有的行，两表完全匹配的行显示成一行，与另一表中没有匹配值时，则视图结果中对应行显示为null；

交叉联接、返回左表中所有的行，左表中的每一行和右表中的所有行组合、交叉联接也被称为笛卡尔积；

今天我们就重点说下内外部联接类型的四种链接，以mysql为例

♥mysql多表查询的四种联接：内联接、左联接、右联接、完全联接

四种方式关键字分别是：inner join、left join、fight join、full join;

下面我们直接通过一个案例直观的明白其中的关联：

a表 (a)	id	name	b表 (b)	id	age	sex	parent_id
1	龙一	1	20	boy	1
2	龙二	2	19	boy	2
3	龙三	3	18	girl	5

a表

(a)

name

b表

(b)

age

sex

parent_id

龙一

boy

龙二

boy

龙三

girl

（其中两表的id和parent_id互为关联的列，从而使两表一一对应）

①、内联接

mysql>select a.*,b.* from a inner join b on a.id=b.parent_id;
-----------------------------
1   龙一   1   20   boy   1
2   龙二   2   19   boy   2
-----------------------------

(内部自然联接查询后，两表的第三行均没有匹配的对应行，所以两表第三行的数据不显示了)

②、左联接

mysql>select a.*,b.* from a left join b on a.id=b.parent_id;
----------------------------
1   龙一   1   20   boy   1
2   龙二   2   19   boy   2
3   龙三   null
----------------------------

(左联接，显示所有左表数据，如果没有匹配右表行则右表数据显示为null，而右表的第三行对应左表没有匹配的数据则不显示)

③、右联接

mysql>select a.*,b.* from a right join b on a.id=b.parent_id;
----------------------------
1   龙一   1   20   boy   1
2   龙二   2   19   boy   2
null       3   18   girl  5
----------------------------

(右联接，显示所有右表数据，如果左表没有匹配值则左表对应视图显示为null，而左表的第三行没有匹配的值则不显示)

④、完全链接

mysql>select a.*,b.* from a full join b on a.id=b.parent_id;
-------------------------------------------------
1   龙一   1   20   boy   1
2   龙二   2   19   boy   2
3   龙三   null
null       3   18   girl  5

（完全链接，可以看出查询结果视图中包含两表的所有数据值）

思考·总结

上面我们知道了内联接，其关键字为 inner join，但是其在多表查询中数据显示不完整，所以外部联接使用比较多。但还有一种联接方式交叉联接，关键字cross join，事实上也不常用；

我们来一起看一下交叉联接的小案例：

mysql> select * from heping.mytable;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data |
| 3 | 003 | 2017-10-30 | i'm third data |
+------+------+------------+----------------------+
3 rows in set (0.00 sec)
mysql> select * from heping.mytable001;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+
1 row in set (0.00 sec)
mysql> select * from heping.mytable,heping.mytable001;
+------+------+------------+----------------------+------+------+------------+----------------------+
| id | name | date | data | id | name | date | data |
+------+------+------------+----------------------+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data      | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 3 | 003 | 2017-10-30 | i'm third data       | 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+------+------+------------+----------------------+
3 rows in set (0.00 sec)
mysql> select * from heping.mytable corss join heping.mytable001;
+------+------+------------+----------------------+------+------+------------+----------------------+
| id | name | date | data | id | name | date | data |
+------+------+------------+----------------------+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data      | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 3 | 003 | 2017-10-30 | i'm third data       | 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+------+------+------------+----------------------+
3 rows in set (0.00 sec)

可以看到简单的交叉联接和普通直接查询，显示结果是一样的，我们在继续观察：

mysql> select * from heping.mytable003;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 2 | 002 | 2017-09-20 | welcom i'm second da |
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+
2 rows in set (0.00 sec)

mysql> select * from heping.mytable corss join heping.mytable003;
+------+------+------------+----------------------+------+------+------------+----------------------+
| id | name | date | data | id | name | date | data |
+------+------+------------+----------------------+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat | 2 | 002 | 2017-09-20 | welcom i'm second da |
| 1 | 001 | 2017-09-19 | welcom i'm first dat | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data      | 2 | 002 | 2017-09-20 | welcom i'm second da |
| 2 | 002 | 2017-10-30 | i'm second data      | 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 3 | 003 | 2017-10-30 | i'm third data       | 2 | 002 | 2017-09-20 | welcom i'm second da |
| 3 | 003 | 2017-10-30 | i'm third data       | 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+------+------+------------+----------------------+
6 rows in set (0.00 sec)

所以交叉联接查询，两表的每行数据都要和另外一个表中的所有行做匹配形成一行，也就是说会形成两表行数相乘的行数，也就是所说的“笛卡尔乘积”；

而如果数据上千上万条，且这种查询没有对应关系所以不常用；当然除此之外，还有一种查询方式：联合查询、关键字union或union all；

联合查询顾名思义就是把多个查询语句的查询结果集中在一起显示：

mysql> select * from mytable union select * from mytable001;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data |
| 3 | 003 | 2017-10-30 | i'm third data |
+------+------+------------+----------------------+
3 rows in set (0.01 sec)
mysql> select * from mytable union select * from mytable003;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data |
| 3 | 003 | 2017-10-30 | i'm third data |
| 2 | 002 | 2017-09-20 | welcom i'm second da |
+------+------+------------+----------------------+
4 rows in set (0.00 sec)
mysql> select * from mytable union all select * from mytable001;
+------+------+------------+----------------------+
| id | name | date | data |
+------+------+------------+----------------------+
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
| 2 | 002 | 2017-10-30 | i'm second data |
| 3 | 003 | 2017-10-30 | i'm third data |
| 1 | 001 | 2017-09-19 | welcom i'm first dat |
+------+------+------------+----------------------+
4 rows in set (0.00 sec)

可以看出，当我们使用关键字union联合查询时，视图结果显示查询罗列所有内容，但是如果两条数据记录相同，则只显示一条数据，如果想所有都显示，则使用关键字“union all”；

所以具体如何查询，还是需要根据不同的需求，斟酌进行匹配查询方式哦；

（好的今天就分享到这里，如果您有高见或好的分享，记得留言哦！）

原创文章，转载请注明：转自于公牛博客

本文链接地址:【数据库】mysql多表查询的四种视图联接方式