MySQL 自学第十四章 使用子查询

 

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） 涉及外部查询的子查询。 用点号进行外部表格查询。