SQL子查询

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'))

上面语句用到了3条SELECT语句。注意，内部的两条查询写一列。

上面的语句是查订购了‘RGAN01’的商品的顾客。

错误示范：

select cust_name,cust_contact from customers
 where cust_id in (select cust_id from  orders
where order_num  in (select order_num,order_item from orderitems 
where prod_id='RGAN01'))

1.如果内部两条查询加一列，就会报错：Operand should contain 1 column(s)。

2使用子查询性能较低。

select cust_name,
       cust_state,
       (select count(*)
         from orders
         where orders.cust_id=customers.cust_id) as orders
from customers
order by cust_name;

orders 是一个计算字段，他对检索出的每个顾客执行一次。上面的字段是为了统计每个顾客的订单数量。