简单易懂教你学会SQL关联子查询
初学SQL的人都会觉得SQL的关联子查询难以理解,为什么?这是有原因的。
关联子查询的执行逻辑和通常的SELECT语句的执行逻辑完成不一样。这就是SQL关联子查询难以理解的原因。
一、书写顺序与执行顺序的差别
我们首先来看看正常情况下SELECT的书写顺序和执行顺序:
书写顺序:
SELECT >> FROM >> WHERE >> GROUP BY >> HAVE >> ORDER BY
执行顺序:
FROM >> WHERE >> GROUP BY >> HAVE >> SELECT >> ORDER BY
以下以Product表为例:
1. 书写过程
执行以下代码说明执行过程:
SELECT product_type,count(*)
FROM Product
WHERE sale_price > 100
GROUP BY product_type
HAVING count(*) > 1
ORDER BY product_type
2. 执行过程
执行过程如下:
-
① FROM product
-
指定从
Product
表获取数据。
② WHERE sale_price > 100
-
筛选销售价大于100的:
③ GROUP BY product_type
-
以
product_type
列分组:
④ HAVING count(*) > 1
-
筛选出组内数据行大于1的
⑤ SELECT product_type,count(*)
-
筛选出
product_type,count(*)
列
⑥ ORDER BY product_type
-
以
product_type
列排序
二、关联子查询的过程
上面我们说明了SELECT语句的书写和执行过程。
我们再来看看以下“选取出各商品种类中高于该商品种类的平均销售单价的商品。” 的关联子查询SELECT语句:
SELECT product_type , product_name, sale_price
FROM Product AS P1
WHERE sale_price > ( SELECT AVG(sale_price)
FROM Product AS P2
WHERE P1.product_type = P2.product_type
GROUP BY product_type);
1. 通常的执行逻辑
-
按照我们正常的
-
①
FROM Product AS P2
:指定从Product获取数据 -
②
WHERE P1.product_type = P2.product_type
:这里的P1和P2都是指向Product表,都是它的别名,所以P1.product_type
与P2.product_type
必然是相等的,所以这个WHERE字句
是废话。 - 既然是废话,我们删除这句废话看看:
select
语句执行逻辑,应该从
WHERE
字句中的括号内的子查询开始,子查询也是一个
SELECT
语句,应该这样开始执行:
SELECT product_type , product_name, sale_price
FROM Product AS P1
WHERE sale_price > (
SELECT AVG(sale_price)
FROM Product AS P2
GROUP BY product_type);
这个语句是错误的,原因是sale_price是一行数据,子查询的结果是3行数据,无法进行比较。更无法得到我们想要的“选取出各商品种类中高于该商品种类的平均销售单价的商品。”这个结果。
2. 实际执行逻辑
实际上,WHERE P1.product_type = P2.product_type
这个语句是关联子查询,关联子查询的执行逻辑和正常的SELECT语句执行逻辑完全不同,这就是它的诡异之处了(不知道规定它的人是怎么想的,搞那么复杂的逻辑)。
下面,我们来说明一下上面的关联子查询代码的执行过程:
记住,关联子查询和正常的SELECT语句完全不同。
① 先执行主查询
SELECT product _type , product_name, sale_price
FROM Product AS P1
结果:
② 从主查询的product _type
先取第一个值='衣服'
,通过WHERE P1.product_type = P2.product_type
传入子查询,子查询变成:
SELECT AVG(sale_price)
FROM Product AS P2
WHERE P2.product_type = '衣服'
GROUP BY product_type);
第一次子查询结果:
③ 从子查询得到的结果AVG(sale_price)=2500
,返回主查询:
SELECT product_type , product_name, sale_price
FROM Product AS P1
WHERE sale_price > 2500 AND product_type = '衣服'
第一次整个语句的结果:
④ 然后,product _type
取第二个值,得到整个语句的第二结果,依次类推,把product _type
全取值一遍,就得到了整个语句的结果集。结果如下:
事实上,所有关联子查询的执行过程都和上面的过程一样。
总结
1、关联子查询的执行逻辑完全不同于正常的SELECT语句。
2、关联子查询执行逻辑如下:
(1)先从主查询的Product表中product _type列取出第一个值,进入子查询中,得到子查询结果,然后返回父查询,判断父查询的where子句条件,则返回整个语句的第1条结果。
(2)重复上述操作,直到所有主查询中的Product表中product _type列记录取完为止。得出整个语句的结果集,就是最后的答案。