我们考虑一个问题,列出订购物品‘RGAN01’的所有顾客的信息,那我们应该用怎样的信息检索?
上面三个步骤每个步骤都可以单独作为一个查询来执行,这就出现了子查询的定义。
不使用子查询“
select order_num
from orderitems
where prod_id = 'RGAN01';
select cust_id
from orders
where order_num in (20007,20008)
使用子查询:
select cust_id
from orders
where order_num in (select order_num
from orderitems
where prod_id = 'RGAN01');
假设有这么一个问题,显示customers表中的每个顾客订单的总数:
select customers.cust_name, customers.cust_state,(
select count(*)
from orders
where orders.cust_id = customers.cust_id
) as orders
from customers
order by cust_name;