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

MySQL联结查询

操作系统:ubuntu 18.04

mysql版本:8.0

创建:2020/7/2

修改:2020/7/2

使用的演示表:

用户表(users)

037b41df6160dbb17bcd8301eae9fb68.png

订单表(orders)

2e9358295a31523134b14106c20ae5fc.png

联结的种类:内联结,左外联结,右外联结,全外联结,自联结,自然联结

内联结(inner join)

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

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

-- 内联结不使用JOIN

SELECT *

FROM users, orders

WHERE users.userId = orders.userId;

-- 内联结使用JOIN

SELECT *

FROM users JOIN orders

ON users.userId = orders.userId;

765133b2296f5c02376c95b7324b963b.png

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

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后结果集中会去除重复的联结列

f693f89b2bf3909d14c6eb538dc63693.png

外联结(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);

8fb7b6e54e432e3fd55c1dd2f525deaa.png

右外联结(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);

16ab75768d6f7f7622a0c22c1b359d7a.png

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

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;

09ef103628e04a99de34e797bb3028b2.png

自然联结(natural join)

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

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

SELECT *

FROM users NATURAL JOIN orders;

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

SELECT *

FROM users JOIN orders USING(userId);

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

11befcbe5703e9818eb107a51525d703.png

自联结(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 = '伞木希美';

6d3bef589643cdeddfdf56c570478d8d.png

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页