(七)数据库 | 子查询与联结查询

TheWordsofLove

上一期我们介绍了分组查询（GROUP BY HAVING）和组合查询（UNION [ALL]），本期介绍数据检索中最后两个非常重要的查询，子查询与联结查询。子查询(SELECT(SELECT))和联结查询（JOIN ON）是数据检索中最强大的两个功能。

在上期第三个任务的第二个实现方法中就已经使用过（内部）联结，它将订单表和订单详情表联结成一张虚表，称之为”联结表“，联结表并不存储在数据库中，我们实施的查询是建立在这张联结表基础之上的！

联结实则是一种机制，它用来关联一条SELECT查询语句中的两个或多个表。内部联结（INNER JOIN ON）是使用最多的连接方式！创建内部联结十分简单，只需要像上面那样指定需要进行联结的表（如orders和orderitems）和用来建立联结的纽带字段（如order_num)即可！

1、查询所有产品的产品名称、价格及对应的供应商名称：由于产品信息和供应商信息分别存储在产品表和供应商表中，所以需要建立联结查询，两表通过键vend_id进行内部联结>>

SELECT vend_name, prod_name, prod_price

FROM vendors, products # 指定需要联结的表

WHERE vendors.vend_id = products.vend_id # 指定联结字段

ORDER BY vend_name, prod_name;

SELECT vend_name, prod_name, prod_price

FROM vendors INNER JOIN products # 指定联结方式(内部)和需要联结的表

ON vendors.vend_id = products.vend_id # 指定连接字段

ORDER BY vend_name, prod_name;

2、类似地，可以建立多表联结：查询编号为20005的订单中的所有产品的产品名称、价格、供应商名称与购买数量，需要联结表订单详情表、产品表和供应商表，其中订单详情表和产品表通过prod_id联结，产品表和供应商表通过vend_id联结>>

注意

1、在建立联结表时，不要忘了用WHERE子句建立联结条件，没有联结条件的表关系返回的联结结果称为”笛卡尔积“，这种无条件联结称为叉联结（cross join），笛卡尔积联结表的数据量为第一个表的数据量×第二个表的数据量，数据量非常恐怖！

2、内部联结（INNER JOIN）又称为等值联结，除了可以用相等操作符（=）建立之外，还可以使用FROM  table1 INNER JOIN table2 ON table1.column = table2.column来建立内部联结。

1、自联结，即利用表别名创建表自身之间的联结，它是内部联结的一个特例，只是用来建立联结表的两个表相同，请看自联结示例：

SELECT COUNT(*) AS prod_quantity

FROM products AS p1, products AS p2 # 产品表通过vend_id进行自联结

WHERE p1.vend_id = p2.vend_id; # 查询结果为66=3^2+2^2+7^2+2^2

自联结在数据检索过程中也是有用的，如进行合格检验时发现ID为DTNTR的产品不合格，现需要对提供该产品的供应商所提供的全部产品进行抽检，则需要查询该供应商的所有产品信息>>

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'

ORDER BY p1.prod_id;

SELECT prod_id, prod_name

FROM products # 使用子查询也是可以的

WHERE vend_id IN (SELECT vend_id 

                                  FROM products

                                  WHERE prod_id = 'DTNTR')

ORDER BY prod_id;

2、外部联结（OUTER JOIN ON）

外部联结是内部联结的扩展，外部联结表中还包含不存在匹配值的数据行（外部联结的声明方式和内部联结完全一样，见示例）！如客户10002没有下过订单，如果使用内部联结联结客户表和订单表，则联结表中不会出现客户10002，如果使用外部联结，则联结表中会给出客户10002，只是该客户不存在订单信息而已>>

SELECT c.cust_id, cust_name, cust_email, order_num, order_date

FROM customers AS c INNER JOIN orders 

ON c.cust_id = orders.cust_id 

ORDER BY c.cust_id; 

# 内部联结，由于orders表中没有顾客10002，联结表中也没有

SELECT c.cust_id, cust_name, cust_email, order_num, order_date

FROM customers AS c LEFT OUTER JOIN orders

ON c.cust_id = orders.cust_id

ORDER BY c.cust_id;

 # 外部联结，由于customers表中含有顾客10002，联结表中就有

检索实例，查询所有顾客的订单总数：

SELECT c.cust_id, cust_name, cust_contact, 

COUNT(order_num) AS order_quantity

FROM customers AS c LEFT OUTER JOIN orders AS o

ON c.cust_id = o.cust_id # 使用外部联结建立联结表后再统计客户的订单数量

GROUP BY c.cust_id

ORDER BY c.cust_id;

注意

1、在使用联结时，必须给出联结条件，否则会返回关系表之间的笛卡尔积！

2、相较于外部联结，内部联结使用更为频繁，两种联结都应该给出正确的联结条件，否则错误的联结表会导致错误的结果！

本期精彩