子查询
子查询即嵌套在其它查询中的查询。
利用子查询进行过滤
下面是两个单独的查询语句：
mysql> SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
mysql> SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
上面两个子查询进行组合：
mysql> SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
最终组合后的语句为：
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'));
作为计算字段使用子查询
mysql> 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 |
+----------------+------------+--------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+--------+
联结表
SQL 最强大的功能之一就是能在做数据检索查询的执行中联结（join）表。
创建联结（使用外键）
mysql> SELECT vend_name, prod_name, prod_price
-> FROM vendors, products
-> WHERE vendors.vend_id = products.vend_id
-> ORDER BY vend_name, prod_name;
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Detonator | 13.00 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
+-------------+----------------+------------+
笛卡尔积
由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name;
应该保证所有联结都有 WHERE 子句。
内部联结
前面所用的联结称为等值联结，它基于两个表之间的相等测试。这种联结也称为内部联结，可以使用其它语法实现：
mysql> SELECT vend_name, prod_name, prod_price
-> FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
| ACME | Detonator | 13.00 |
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
+-------------+----------------+------------+
SQL 规范推荐使用 INNER JOIN。
创建高级联结
使用表别名
使用表别名的理由为：
缩短 SQL 语句
允许在单条 SELECT 语句中多次使用相同的表
SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems as oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';
自联结
查询 prod_id 为 DTNTR 产品所属的供应商的所有产品：
mysql> SELECT prod_id, prod_name
-> FROM products
-> WHERE vend_id = (SELECT vend_id
-> FROM products
-> WHERE prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
使用自联结：
mysql> SELECT p1.prod_id, p1.prod_name
-> FROM products AS p1, products AS p2
-> WHERE p1.vend_id = p2.vend_id
-> AND p2.prod_id = 'DTNTR';
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，虽然最终的结果是相同的，但有时候处理联结远比处理子查询快的多。
自然联结
SELECT c.*, o.order_num, o.order_date,
oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'FB'
外部联结
检索所有订单的客户：
mysql> SELECT customers.cust_id, orders.order_num
-> FROM customers INNER JOIN orders
-> ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
使用外部联结，检索所有客户，包括那些没订单的。
mysql> SELECT customers.cust_id, orders.order_num
-> FROM customers LEFT OUTER JOIN orders
-> ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10002 | NULL |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
存在两种基本的外部联结形式：左外部联结和右外部联结，左联结以左为主，右联结以右为主，没有补 null。
使用带聚合函数的联结
mysql> SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
-> FROM customers LEFT JOIN orders ON customers.cust_id = orders.cust_id
-> GROUP BY customers.cust_id;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Mouse House | 10002 | 0 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+
