mysql查询速度联结_MySQL联结查询

MySQL联结查询

操作系统：ubuntu 18.04

mysql版本：8.0

创建：2020/7/2

修改：2020/7/2

使用的演示表：

用户表(users)

订单表(orders)

联结的种类：内联结，左外联结，右外联结，全外联结，自联结，自然联结

内联结(inner join)

内联结也被称为等值联结，是将两张或多张表通过联结列进行匹配联结为一张表。以联结两个表为例，指定一列或多列作为联结列，使用第一张表中一行中的指定列与第二张表中每一行对应的列比较，如果这些列都与对应列相等，那么将这两行拼接加入联结表。在第二张表中找不到匹配的行不会出现在联结表中。联结表行数可能会超过第一张表，因为在第二张表中可能有多行与第一张表中的一行匹配。

内联结有两种写法，两种写法结果一样，使用join的写法更加标准

-- 内联结不使用JOIN

SELECT *

FROM users, orders

WHERE users.userId = orders.userId;

-- 内联结使用JOIN

SELECT *

FROM users JOIN orders

ON users.userId = orders.userId;

当不使用联结条件时，返回的结果是笛卡尔积，也就是第一张表中的每一行和第二张表中所有行匹配，最终结果的行数为第一张表行数乘以第二张表的行数

SELECT *

FROM users JOIN orders;

在使用多个列联结或者联结多张表时，可以使用AND拼接条件

SELECT *

FROM users JOIN orders JOIN order_items

ON users.userId = orders.userId AND orders.orderId = order_items.orderId;

在MySQL中还可以进行简写

SELECT *

FROM users JOIN orders USING(userId);

简写的条件是在两张表中联结列的列名是相同的，使用USING后结果集中会去除重复的联结列

外联结(outer join)

外联结与内联结相似，区别是外联结将在另一个表中没有匹配的行与全是null值的行匹配

外联结分为：左外连接，右外联结，全外联结

左外联结(left outer join)

以左边表做主表，左边的表中不能与右边表匹配的行，与全是null值的行匹配并加入联结表。

这样左边表中的所有行都在联结表中至少出现了一次

-- 使用ON

SELECT *

FROM users LEFT OUTER JOIN orders

ON users.userId = orders.userId;

-- 使用USING

SELECT *

FROM users LEFT OUTER JOIN orders USING(userId);

右外联结(right outer join)

右外联结以右边的表作为主表，右边表中不能在左边表匹配的行，与全是null值的行匹配。

这样右边表中所有的行都在联结表中至少出现了一次

-- 使用ON

SELECT *

FROM users RIGHT OUTER JOIN orders

ON users.userId = orders.userId;

-- 使用USING

SELECT *

FROM users RIGHT OUTER JOIN orders USING(userId);

左外联结和右外联结区别仅在顺序，比如下面两句效果相同

A LEFT OUTER JOIN B;

B RIGHT OUTER JOIN A;

全外联结(full outer join)

全外联结是将左右两张表中，不能与另一张表中行匹配的行都与全是null值的行匹配，MySQL不支持FULL OUTER JOIN，可以使用UNION实现，UNION会去除那些重复的行

-- 这里不能使用USING，使用USING会使两个结果集中列的对应关系错乱

SELECT *

FROM users LEFT OUTER JOIN orders

ON users.userId = orders.userId

UNION

SELECT *

FROM users RIGHT OUTER JOIN orders

ON users.userId = orders.orderId;

自然联结(natural join)

自然联结是一种特殊的等值联结。在联结两个表时，使用两个表的公共列进行联结，公共列是指列名和值类型都相同的列，并且在结果中会去除冗余的联结列。

-- 使用NATURAL JOIN进行自然联结

SELECT *

FROM users NATURAL JOIN orders;

-- 上面语句的结果，与此句使用内联结的结果相同

SELECT *

FROM users JOIN orders USING(userId);

上面的例子因为users和orders表中都有userId这个列，并且值类型也相同，所以使用userId进行联结。在自然联结中不能使用ON或 USING来限制

自联结(self join)

自联结就是一张表和自己联结，比如要找出users表中和伞木希美同一个年龄的用户，我们可以使用子查询先得到伞木希美的年龄，再查询这个年龄的所有用户，也可以使用自联结完成此项工作

-- 使用子查询

SELECT *

FROM users

WHERE age = (

SELECT age

FROM users

WHERE name = '伞木希美'

);

-- 使用自联结

SELECT u.*

FROM users JOIN users AS u USING(age)

WHERE users.name = '伞木希美';