11.1 子查询
查询（query），任何SQL语句都是查询。但此术语一般指SELECT语句。
SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。
作为子查询的SELECT语句只能查询单个列，否则会返回错误。
11.2 利用子查询进行过滤
在SELECT语句中，子查询总是从内向外处理。
MariaDB [sqlbzbh]> SELECT * FROM OrderItems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | BR01 | 100 | 5.49 |
| 20005 | 2 | BR03 | 100 | 10.99 |
| 20006 | 1 | BR01 | 20 | 5.99 |
| 20006 | 2 | BR02 | 10 | 8.99 |
| 20006 | 3 | BR03 | 10 | 11.99 |
| 20007 | 1 | BR03 | 50 | 11.49 |
| 20007 | 2 | BNBG01 | 100 | 2.99 |
| 20007 | 3 | BNBG02 | 100 | 2.99 |
| 20007 | 4 | BNBG03 | 100 | 2.99 |
| 20007 | 5 | RGAN01 | 50 | 4.49 |
| 20008 | 1 | RGAN01 | 5 | 4.99 |
| 20008 | 2 | BR03 | 5 | 11.99 |
| 20008 | 3 | BNBG01 | 10 | 3.49 |
| 20008 | 4 | BNBG02 | 10 | 3.49 |
| 20008 | 5 | BNBG03 | 10 | 3.49 |
| 20009 | 1 | BNBG01 | 250 | 2.49 |
| 20009 | 2 | BNBG02 | 250 | 2.49 |
| 20009 | 3 | BNBG03 | 250 | 2.49 |
+-----------+------------+---------+----------+------------+
18 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT * FROM Orders;
+-----------+---------------------+------------+
| order_num | order_date | cust_id |
+-----------+---------------------+------------+
| 20005 | 2012-05-01 00:00:00 | 1000000001 |
| 20006 | 2012-01-12 00:00:00 | 1000000003 |
| 20007 | 2012-01-30 00:00:00 | 1000000004 |
| 20008 | 2012-02-03 00:00:00 | 1000000005 |
| 20009 | 2012-02-08 00:00:00 | 1000000001 |
+-----------+---------------------+------------+
5 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';
+-----------+
| order_num |
+-----------+
| 20007 |
| 20008 |
+-----------+
2 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT cust_id FROM Orders WHERE order_num IN (20007,20008);
+------------+
| cust_id |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');
+------------+
| cust_id |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT * FROM Customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys | 200 Maple Lane | Detroit | MI | 44444 | USA | John Smith | sales@villagetoys.com |
| 1000000002 | Kids Place | 333 South Lake Drive | Columbus | OH | 43333 | USA | Michelle Green | NULL |
| 1000000003 | Fun4All | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | jjones@fun4all.com |
| 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> SELECT cust_name,cust_contact FROM Customers WHERE cust_id IN ('1000000004','1000000005');
+---------------+--------------------+
| cust_name | cust_contact |
+---------------+--------------------+
| Fun4All | Denise L. Stephens |
| The Toy Store | Kim Howard |
+---------------+--------------------+
2 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
MariaDB [sqlbzbh]> 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'));
+---------------+--------------------+
| cust_name | cust_contact |
+---------------+--------------------+
| Fun4All | Denise L. Stephens |
| The Toy Store | Kim Howard |
+---------------+--------------------+
2 rows in set (0.00 sec)
MariaDB [sqlbzbh]>
11.3 作为计算字段使用子查询
在这种构造中，必须严格限制有歧义的列。可通过完全限定列名的方法实现。
完全限定列名，就是同时指定表名和列名，并用一个句点分隔。
推荐的方式：如果SELECT语句中操作多个表，使用完全限定列名来避免歧义。
MariaDB [sqlbzbh]> SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;
+---------------+------------+--------+
| cust_name | cust_state | orders |
+---------------+------------+--------+
| Fun4All | IN | 1 |
| Fun4All | AZ | 1 |
| Kids Place | OH | 0 |
| The Toy Store | IL | 1 |
| Village Toys | MI | 2 |
+---------------+------------+--------+
5 rows in set (0.00 sec)
MariaDB [sqlbzbh]>