1.利用子查询进行过滤
子查询总是从内向外处理
#列出订购TNT2的全部客户信息
SELECT * FROM customers WHERE cust_id IN (
SELECT cust_id FROM orders WHERE order_num IN (
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'))
2.作为计算字段使用子查询
#列出每个客户订单数量
SELECT cust_name,
(SELECT COUNT(*) FROM orders
WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name
#比上面的好理解一点
SELECT a.cust_id,a.cust_name,b.orders
FROM customers a
LEFT JOIN
(SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id) b
ON b.cust_id=a.cust_id
相关子查询:涉及外部的子查询
任何时候只要列名可能有多义性,就要使用这种语法
orders.cust_id=customers.cust_id 列名相同时,前面要加上表名