mysql默认join是什么类型_理解 SQL 中的各类 JOIN

最近比较头疼 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 语法

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 可以理解为以下的集合操作结果中的列：进行 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 中，这里的三个关键词是等价的In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent.

我们也可以从实际查询中看出等价性；

-- 用 JOINmysql> 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 JOINmysql> 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 JOINmysql> 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 结果的区别。个人能力有限，可能会有错误，欢迎指出和探讨。(封面图片

更多阅读资料What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?​stackoverflow.comWhat is the difference between "INNER JOIN" and "OUTER JOIN"?​stackoverflow.com

参考