所谓 SQL 子查询就是嵌套在其他查询中的查询。子查询通常用于 WHERE 子句的 IN 操作符中进行过滤,以及用来填充计算列。下面我们从这两种使用场景展开学习。
本节涉及到关系表如下:
最上方的订单表 Orders 存储了订单 ID 、订单日期以及顾客 ID;具体的订单信息存储在 OrderItems 表中,通过 order_num
进行关联;具体的顾客信息存储在顾客表 Customers 中,通过 cust_id
字段进行关联。
使用子查询进行过滤
下面,假设我们需要检索出购买了 RGA01 产品的所有顾客信息,应该怎么做呢?
首先,我们只列出步骤:
- 找到包含了产品 RGA01 的所有订单的订单 ID;
- 通过订单 ID 找到所有的顾客 ID;
- 最后根据上一步检索出的顾客 ID 检索出顾客信息。
接下来,我们来完成第一步:
SELECT
order_num
FROM
OrderItems
WHERE
prod_id = 'RGAN01';
包含了产品 RGAN01
的订单编号:
有了订单编号,就可以从订单表检索出顾客 ID 了:
SELECT
cust_id
FROM
Orders
WHERE
order_num IN ( 20007, 20008 );
运行结果:
接下来,我们来合并上面的两步:把第一个查询变为子查询,放在 WHERE 语句的 IN 操作符之后:
SELECT
cust_id
FROM
Orders
WHERE
order_num IN ( SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01' );
运行结果和上述第二步获得的结果是一样的:
在 SELECT 语句中子查询从内向外处理:首先执行 SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'
子查询,将返回的订单号作为 IN 操作符的参数,执行外部查询:SELECT cust_id FROM Orders WHERE order_num IN ( 20007, 20008 );
。
最后,我们来看第三步,根据顾客 ID 检索出顾客相关信息。
SELECT
cust_name,
cust_contact
FROM
Customers
WHERE
cust_id IN ( 1000000004, 1000000005 );
运行结果:
同理,我们可以将上述的 WHERE 子句替换为子查询:
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN ( SELECT cust_id
FROM Orders
WHERE order_num IN ( SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01' ));
至此,我们的检索任务就完成了:查询购买了 RGAN01 商品的所有顾客姓名及联系方式。
注 1:作为子查询的 SELECT 语句只能返回单个字段,否则将发生语法错误。
注 2:上述的检索任务,总共需要执行三条 SELECT 语句,嵌套的子句越多,性能也就越差。因此,子查询并不是执行这类查询的最优方案,下一篇文章小鱼将和大家学习联结表来再次探讨同一个问题。
使用子查询创建计算字段
子查询的另一个使用场景为创建计算字段。同样,我们以一个案例来学习:检索每个顾客的订单的总数。
顾客表与订单表是一对多的关系,即一个顾客对应多个订单,但一个订单只对应一个顾客。
如下的 SQL 检索出顾客 1000000001
的订单数:
SELECT
COUNT( * ) AS orders
FROM
Orders
WHERE
cust_id = 1000000001;
要对每个顾客的订单计数,应该将其作为子查询:
SELECT
cust_name,
cust_state,
( SELECT COUNT( * ) FROM Orders WHERE Orders.cust_id = Customers.cust_id ) AS orders
FROM
Customers
ORDER BY
cust_name;
运行结果:
上述 SQL 对 Customers 表中每个顾客返回三列:cust_name
cust_state
和 orders
。其中 orders
为子查询创建的计算字段,该子查询对检索出的每个顾客都执行一次,一共执行了 6 次子查询。
注:使用子查询创建计算字段的检索效率是比较糟糕的,在顾客表数量庞大的情况下,将会执行非常多次的子查询。
在子查询的条件中,我们使用了表的完全限定列名 Orders.cust_id = Customers.cust_id
,这是因为 Orders
表和 Customers
表中包含了字段名名称相同的列。
注:如果在 SELECT 语句中操作多张表,应该使用完全限定列名来避免歧义。
总结
本节我们学习了在 SELECT 语句中使用子查询的两种用法:将子查询应用于 WHERE 子句的 IN 操作符中,进行条件过滤,以及用子查询创建计算字段。
子查询的检索效率不够理想,下一篇文章中小鱼将和大家展开联结表的学习~连接表 是数据检索的精华和重点,我们拭目以待吧!
网友评论