![4c35d2ba334e9a7d63b28f3b33eb7dec.png](https://i-blog.csdnimg.cn/blog_migrate/7cdd1b2985166db0106610f351eb32f8.jpeg)
最近比较头疼 JOIN 的用法和结果,写这篇文章是为了总结归纳阅读的资料。在本文中,将以 MySQL 语法为例。
例子中使用的表
我们有两张表,分别为 product 与 price,结构如下所示 (price 表中的 id 是外键):
mysql> desc product;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc price;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | smallint(6) | YES | | NULL | |
| pprice | decimal(5,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
我们在表里插入了一些数据:
mysql> select * from product;
+----+--------+
| id | name |
+----+--------+
| 0 | milk |
| 1 | coffee |
| 2 | water |
+----+--------+
3 rows in set (0.00 sec)
mysql> select * from price;
+------+--------+
| id | pprice |
+------+--------+
| 0 | 10.90 |
| 1 | 28.00 |
| 2 | 1.99 |
+------+--------+
3 rows in set (0.00 sec)
MySQL 中的 JOIN 类型
下面是 MySQL 8.0 中的 JOIN 语法[1]:
joined_table: {
table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
| table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}
根据语法,我们可以有多少种组合呢(按 JOIN 关键词算)?
=> 答案是 4+4+6+1=15(算错了?请留言)
但是这些 JOIN 都大同小异,主要有以下几类:
- NATURAL JOIN
- LEFT(OUTER)/RIGHT(OUTER) JOIN
- JOIN/INNER/CORSS JOIN
我们下来按顺序讨论,从 natural join 开始 。
NATURAL JOIN
先来看两个例子:
mysql> select * from product natural join price;
+----+--------+--------+
| id | name | pprice |
+----+--------+--------+
| 0 | milk | 10.90 |
| 1 | coffee | 28.00 |
| 2 | water | 1.99 |
+----+--------+--------+
3 rows in set (0.00 sec)
-- 给 produc 添加一行,但是在 price 没有添加对应的行
mysql> insert into product value(3, 'juice');
Query OK, 1 row affected (0.01 sec)
mysql> select * from product natural join price;
+----+--------+--------+
| id | name | pprice |
+----+--------+--------+
| 0 | milk | 10.90 |
| 1 | coffee | 28.00 |
| 2 | water | 1.99 |
+----+--------+--------+
3 rows in set (0.00 sec)
在这两条查询的结果中,有以下特征:
- 只保留一列 id
- price 不包含的 id 没有出现
这些特征就是 NATURAL JOIN 本身的含义:
- 名称相同的列为 JOIN 使用的列
- 某行所对应该该列的值相等为 JOIN 的条件
NATURAL JOIN 可以理解为以下的集合操作[2]:
- 结果中的列:进行 JOIN 两张表列的并集
- 结果中的行:两张表中满足值相等的行的并集
LEFT/RIGHT JOIN
在 MySQL 中,LEFT JOIN = LEFT OUTER JOIN;RIGHT JOIN = RIGHT OUTER JOIN。
首先要说明的是,这两个 JOIN 不是对称的(结果不相同):
mysql> select * from product right join price on product.id = price.id;
+------+--------+------+--------+
| id | name | id | pprice |
+------+--------+------+--------+
| 0 | milk | 0 | 10.90 |
| 1 | coffee | 1 | 28.00 |
| 2 | water | 2 | 1.99 |
+------+--------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from product left join price on product.id = price.id;
+----+--------+------+--------+
| id | name | id | pprice |
+----+--------+------+--------+
| 0 | milk | 0 | 10.90 |
| 1 | coffee | 1 | 28.00 |
| 2 | water | 2 | 1.99 |
| 3 | juice | NULL | NULL |
+----+--------+------+--------+
4 rows in set (0.00 sec)
对于 LEFT JOIN(RIGHT JOIN 类似),结果保留左表 product 中 id 的值 {0,1,2,3,},因为右表 price 没有 id=3 的行,所以当 id=3 时,结果中右边的值为 NULL。
特征如下:
- 结果中的列:保留左表和右表中的所有列。
- 结果中的行:保留左表所有行,根据给定的 JOIN 条件,取两表中满足该条件行的并集;如果右表行不满足条件,值为 NULL
练习题:以下两条查询的结果是什么?
select * from product natural left join price;
select * from product natural right join price;
JOIN/INNER/CROSS JOIN
在 MySQL 中,这里的三个关键词是等价的[1]:
In MySQL,JOIN
,CROSS JOIN
, andINNER JOIN
are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent.
我们也可以从实际查询中看出等价性;
-- 用 JOIN
mysql> select * from product join price on product.id = price.id;
+----+--------+------+--------+
| id | name | id | pprice |
+----+--------+------+--------+
| 0 | milk | 0 | 10.90 |
| 1 | coffee | 1 | 28.00 |
| 2 | water | 2 | 1.99 |
+----+--------+------+--------+
3 rows in set (0.00 sec)
-- 用 INNER JOIN
mysql> select * from product inner join price on product.id = price.id;
+----+--------+------+--------+
| id | name | id | pprice |
+----+--------+------+--------+
| 0 | milk | 0 | 10.90 |
| 1 | coffee | 1 | 28.00 |
| 2 | water | 2 | 1.99 |
+----+--------+------+--------+
3 rows in set (0.00 sec)
-- 用 CROSS JOIN
mysql> select * from product cross join price on product.id = price.id;
+----+--------+------+--------+
| id | name | id | pprice |
+----+--------+------+--------+
| 0 | milk | 0 | 10.90 |
| 1 | coffee | 1 | 28.00 |
| 2 | water | 2 | 1.99 |
+----+--------+------+--------+
3 rows in set (0.00 sec)
不加 JOIN 条件的运行结果,以 JOIN 关键词为例:
mysql> select * from product join price;
+----+--------+------+--------+
| id | name | id | pprice |
+----+--------+------+--------+
| 0 | milk | 2 | 1.99 |
| 0 | milk | 1 | 28.00 |
| 0 | milk | 0 | 10.90 |
| 1 | coffee | 2 | 1.99 |
| 1 | coffee | 1 | 28.00 |
| 1 | coffee | 0 | 10.90 |
| 2 | water | 2 | 1.99 |
| 2 | water | 1 | 28.00 |
| 2 | water | 0 | 10.90 |
| 3 | juice | 2 | 1.99 |
| 3 | juice | 1 | 28.00 |
| 3 | juice | 0 | 10.90 |
+----+--------+------+--------+
12 rows in set (0.01 sec)
此处可以理解为 JOIN 条件默认是 True。
特征:
- 结果的列:左表和右表的所有列
- 结果的行:结果中的一行是左表的一行和右表一行的并集
特殊情况
当我们不给定关键词,使用逗号分隔时,MySQL 默认会转换为这里的三个 JOIN:
This is a conservative extension if each comma in a list of table_reference items is considered as equivalent to an inner join.
练习题:以下查询的结果是什么?
select * from product inner join price on 1;
select * from product inner join price on 0;
select * from product natural inner join price;
总结
我们在本文中以 MySQL 为例,介绍了不同的 JOIN ,同时使用集合概念表述了不同 JOIN 结果的区别。个人能力有限,可能会有错误,欢迎指出和探讨。(封面图片[3])
更多阅读资料
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?stackoverflow.com![3b5a4c0abf740581bbc4b4ffc02df363.png](https://i-blog.csdnimg.cn/blog_migrate/fc9f1c1e52571c6122c5acb1bcc9f3fd.png)
![3b5a4c0abf740581bbc4b4ffc02df363.png](https://i-blog.csdnimg.cn/blog_migrate/fc9f1c1e52571c6122c5acb1bcc9f3fd.png)
参考
- ^abMySQl JOIN Clause https://dev.mysql.com/doc/refman/8.0/en/join.html
- ^SQL and Relational Theory: How to Write Accurate SQL Code. P.185 https://www.amazon.com/SQL-Relational-Theory-Write-Accurate/dp/1491941170
- ^封面图片 https://data-flair.training/blogs/sql-join/