1、子查询
SQL还允许创建子查询(subquery),即嵌套在其他查询中的查询。
2、利用子查询进行过滤
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
通过将两个数据表展示,可以看到,子查询的顺序由内向外
3层子查询
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 = 'TNT2'));
3、作为计算字段使用子查询
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
相关子查询(correlated subquery) 涉及外部查询的子查询。 用点号进行外部表格查询。