使用子查询
查询：任何SQL语句都是查询，但此术语一般指SELECT语句
1）列出订购物品TNT2的所有客户：
法1:
SELECT order_num
FROM orderitems
WHERE prod_id=’TNT2’;
SELECT cust_id
FROM orders
WHERE order_num IN(20005,20007);
SELECT custname,custcontact
FROM customers
WHERE cust_id IN(10001,10004);
法2:(在SELECT语句中，子查询总是从内向外处理)
SELECT custname,custcontact
FROM customers
WHERE custid IN(SELECT custid
FROM orders
WHERE ordernum IN(SELECT ordernum
FROM orderitems
WHERE prod_id=’TNT2’));
法3:
SELECT custname,custcontact
FROM customers,orders,orderitems
WHERE customers.custid=orders.custid
AND orderitems..ordernum=orders.ordernum
AND prod_id=’TNT2’;
2)显示customers表中每个客户的订单总数
SELECT custname,custstate,
(SELECT COUNT(*)
FROM orders
WHERE orders.custid=customers.custid) AS orders
FROM customers
ORDER BY cust_name;
相关子查询：涉及外部查询的子查询