SQL 关联子查询 执行过程 个人理解

表创建

CREATE TABLE Product
(product_id      CHAR(4)      NOT NULL,
 product_name    VARCHAR(100) NOT NULL,
 product_type    VARCHAR(32)  NOT NULL,
 sale_price      INTEGER ,
 purchase_price  INTEGER ,
 regist_date     DATE ,
 PRIMARY KEY (product_id));

 

数据准备

【SQLServer,PostgreSQL】
BEGIN TRANSACTION;
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服',1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品',500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服',4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具',3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具',6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具',500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具',880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品',100, NULL,'2009-11-11');
COMMIT;

在MySQL中运行时，需要把BEGIN TRANSACTION;改写成START TRANSACTION;
在Oracle和DB2中运行时，无需使BEGIN TRANSACTION;（请予以删除）。

 

【sql中表和表的查询概念理解，经常用到数学上的组合关系。执行过程理解上是一行一行执行的，它不是整列一下子执行。列名都可以看成变量 ，随着执行 在sql中在不断改变值】

SELECT * FROM Product;

 product_id | product_name | product_type | sale_price | purchase_price | regist_date
------------+--------------+--------------+------------+----------------+-------------
 0001       | T恤          | 衣服         |       1000 |            500 | 2009-09-20
 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11
 0003       | 运动T恤      | 衣服         |       4000 |           2800 |
 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2009-09-20
 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2009-01-15
 0006       | 叉子         | 厨房用具     |        500 |                | 2009-09-20
 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2008-04-28
 0008       | 圆珠笔       | 办公用品     |        100 |                | 2009-11-11
(8 行记录)

 

显示效果，按照product_type 来分组，显示大于自身组内平均值的价格

 

【以下是通过关联子查询得到的效果】
SELECT product_type, product_name, sale_price
FROM Product AS P1 -- -------① 外部表 取别名 P1
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product AS P2 -- -------② 内部子表 取别名 P2
WHERE P1.product_type = P2.product_type -- -------关联子查询需要的判断条件，内外在条件上关联。
 );

【最终查询结果，都是大于各自组内售价平均值的行】

 product_type | product_name | sale_price
--------------+--------------+------------
 办公用品     | 打孔器       |        500
 衣服         | 运动T恤      |       4000
 厨房用具     | 菜刀         |       3000
 厨房用具     | 高压锅       |       6800
(4 行记录)

 

 

实际执行过程是从里到外来执行的，理解可以从外到里来理解。

【把sql拆分成里和外，先外部执行得到的结果】
SELECT product_type, product_name, sale_price
FROM Product;
 product_type | product_name | sale_price
--------------+--------------+------------
 衣服         | T恤          |       1000
 办公用品     | 打孔器       |        500
 衣服         | 运动T恤      |       4000
 厨房用具     | 菜刀         |       3000
 厨房用具     | 高压锅       |       6800
 厨房用具     | 叉子         |        500
 厨房用具     | 擦菜板       |        880
 办公用品     | 圆珠笔       |        100
(8 行记录)
查询出来的表 作为P1

【内部】

子查询中关联了外部表 P1.product_type，所以将第一条记录转到子查询，进行循环对比P1.product_type = P2.product_type，相当于 P1.衣服= P2.product_type，得到P2中所有衣服的平均值。

内部相当于执行了一次：SELECT  AVG(sale_price)   FROM  Product  WHERE  '衣服' = product_type ，返回一个平均值符合子查询的单值条件(只能返回一个值)。
          avg
-----------------------
 2500.0000000000000000
(1 行记录)

外部就得到了这么个值：
SELECT product_type, product_name, sale_price
FROM Product
WHERE sale_price >2500;--  如果执行到的是第一个“衣服”，那么sale_price的当前值应该是1000，这个“衣服”会被排除掉，不会在最终结果中显示。下一次循环就是“办公用品”。内部子查询条件变为 WHERE  P1.办公用品== P2.product_type，得到P2中所有办公用品的平均值。以此类推。整体每次执行看起来都是外部当前的 产品类型的售价 是否大于 内部这个产品类型 的平均值 ，里外一致通过内部WHERE条件关联好了。

其实可以想象成两张表，因为外部表和内部表是同一张表。
【P1】
 product_type | product_name | sale_price
--------------+--------------+------------
 衣服         | T恤          |       1000  ←   从衣服开始执行 
 办公用品     | 打孔器       |        500
 衣服         | 运动T恤      |       4000
 厨房用具     | 菜刀         |       3000
 厨房用具     | 高压锅       |       6800
 厨房用具     | 叉子         |        500
 厨房用具     | 擦菜板       |        880
 办公用品     | 圆珠笔       |        100

按照P2的条件来对比
【P2】
 product_type | product_name | sale_price
--------------+--------------+------------
 衣服         | T恤          |       1000 ← P1.衣服 = P2.衣服
 办公用品     | 打孔器       |        500 ← P1.衣服 = P2.办公用品 
 衣服         | 运动T恤      |       4000 ← P1.衣服 = P2.衣服
 厨房用具     | 菜刀         |       3000 ...依次比较下去，一个循环结束，把符合条件的求平均值
 厨房用具     | 高压锅       |       6800 
 厨房用具     | 叉子         |        500
 厨房用具     | 擦菜板       |        880
 办公用品     | 圆珠笔       |        100

【总结：内外表的关联查询过程，就像个嵌套for循环

for(){

    for(){
    
    }
}

内循环执行完了就执行外部循环，也就是当第一次内循环体执行完以后外层循环才进入第二次循环，以此类推。

】