数据库查询技巧：联结、自联结、外部联结与UNION-优快云博客

本文探讨了数据库查询中的四种关键操作：表联结、自联结、外部联结和组合查询UNION。通过实际案例，解释了如何查询多个表之间的关联信息，如何处理未匹配的数据，以及如何合并不同查询结果。特别是，UNION操作用于合并结果集，而UNION ALL则保留所有记录，包括重复行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库查询延伸51

表联结
自联结
外部联结
组合查询UNION

表联结

表关系分为一对一、一对多、多对多三种。

表联结：在一条select语句中联结多个表。

案例：现在有两个表：vendors、products分别存储供应商信息、产品信息。

如何查询所有的产品及其供应商信息？

运用表联结：

select prod_id, vend_name from vendors,products where vendors.vend_id = products.vend_id;
+---------+-------------+
| prod_id | vend_name   |
+---------+-------------+
| ANV01   | Anvils R Us |
| ANV02   | Anvils R Us |
| ANV03   | Anvils R Us |
| FU1     | LT Supplies |
| OL1     | LT Supplies |
| DTNTR   | ACME        |
| FB      | ACME        |
| FC      | ACME        |
| SAFE    | ACME        |
| SLING   | ACME        |
| TNT1    | ACME        |
| TNT2    | ACME        |
| JP1000  | Jet Set     |
| JP2000  | Jet Set     |
+---------+-------------+
14 rows in set (0.00 sec)

还可以使用join（或者是inner join）：

select prod_id, vend_name from vendors inner join products on vendors.vend_id = products.vend_id;
+---------+-------------+
| prod_id | vend_name   |
+---------+-------------+
| ANV01   | Anvils R Us |
| ANV02   | Anvils R Us |
| ANV03   | Anvils R Us |
| FU1     | LT Supplies |
| OL1     | LT Supplies |
| DTNTR   | ACME        |
| FB      | ACME        |
| FC      | ACME        |
| SAFE    | ACME        |
| SLING   | ACME        |
| TNT1    | ACME        |
| TNT2    | ACME        |
| JP1000  | Jet Set     |
| JP2000  | Jet Set     |
+---------+-------------+
14 rows in set (0.00 sec)

如何查询产品价格信息及其供应商信息？

select item_price, products.prod_id, vend_name 
from orderitems, products,vendors 
where orderitems.prod_id  = products.prod_id 
and 
products.vend_id = vendors.vend_id;
+------------+---------+-------------+
| item_price | prod_id | vend_name   |
+------------+---------+-------------+
|       5.99 | ANV01   | Anvils R Us |
|       9.99 | ANV02   | Anvils R Us |
|      10.00 | TNT2    | ACME        |
|      10.00 | FB      | ACME        |
|      55.00 | JP2000  | Jet Set     |
|      10.00 | TNT2    | ACME        |
|       2.50 | FC      | ACME        |
|      10.00 | FB      | ACME        |
|       8.99 | OL1     | LT Supplies |
|       4.49 | SLING   | ACME        |
|      14.99 | ANV03   | Anvils R Us |
+------------+---------+-------------+
11 rows in set (0.01 sec)

还可以使用join（或者是inner join):

select item_price, products.prod_id, vend_name 
from orderitems 
join products on orderitems.prod_id  = products.prod_id 
join vendors on products.vend_id = vendors.vend_id;
+------------+---------+-------------+
| item_price | prod_id | vend_name   |
+------------+---------+-------------+
|       5.99 | ANV01   | Anvils R Us |
|       9.99 | ANV02   | Anvils R Us |
|      10.00 | TNT2    | ACME        |
|      10.00 | FB      | ACME        |
|      55.00 | JP2000  | Jet Set     |
|      10.00 | TNT2    | ACME        |
|       2.50 | FC      | ACME        |
|      10.00 | FB      | ACME        |
|       8.99 | OL1     | LT Supplies |
|       4.49 | SLING   | ACME        |
|      14.99 | ANV03   | Anvils R Us |
+------------+---------+-------------+
11 rows in set (0.00 sec)

自联结

如果我们发现某一件产品有问题，想要找出对应的生产商生产的其他商品的信息，怎么办？

先使用子查询(嵌套查询)：

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) |
+---------+----------------+
7 rows in set (0.00 sec)



这里用到了自联结：

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) |
+---------+----------------+
7 rows in set (0.00 sec)

实际上，自联结是表名重命名的一种用法。

还可以使用join（或者是inner join）:

select p1.prod_id, p1.prod_name from products as p1 join products as p2 on p1.vend_id = p2.vend_id where 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) |
+---------+----------------+
7 rows in set (0.00 sec)

外部联结

案例一：对每个客户下了多少订单进行统计，包含未下订单的客户。

select customers.cust_name, customers.cust_id, count(orders.order_num) num_order from customers left join orders on customers.cust_id = orders.cust_id group by customers.cust_id;
+----------------+---------+-----------+
| cust_name      | cust_id | num_order |
+----------------+---------+-----------+
| Coyote Inc.    |   10001 |         2 |
| Mouse House    |   10002 |         0 |
| Wascals        |   10003 |         1 |
| Yosemite Place |   10004 |         1 |
| E Fudd         |   10005 |         1 |
+----------------+---------+-----------+
5 rows in set (0.00 sec)

案例二：列出所有产品及其订购数量，包括没有订购的产品。

select products.prod_name, products.prod_id , count(orderitems.order_num) as num_ord from products left join orderitems on products.prod_id = orderitems.prod_id group by products.prod_id;
+----------------+---------+---------+
| prod_name      | prod_id | num_ord |
+----------------+---------+---------+
| .5 ton anvil   | ANV01   |       1 |
| 1 ton anvil    | ANV02   |       1 |
| 2 ton anvil    | ANV03   |       1 |
| Detonator      | DTNTR   |       0 |
| Bird seed      | FB      |       2 |
| Carrots        | FC      |       1 |
| Fuses          | FU1     |       0 |
| JetPack 1000   | JP1000  |       0 |
| JetPack 2000   | JP2000  |       1 |
| Oil can        | OL1     |       1 |
| Safe           | SAFE    |       0 |
| Sling          | SLING   |       1 |
| TNT (1 stick)  | TNT1    |       0 |
| TNT (5 sticks) | TNT2    |       2 |
+----------------+---------+---------+
14 rows in set (0.01 sec)

组合查询UNION

包含两条或两条以上的select语句，每条语句用UNION隔开；

每条查询语句必须含有相同的列（列的顺序尽量保持一致）；

列的排序不存在一部分列按照一种排序方式，另一部分按照另一种排序方式。都是在语句的最后统一排序。

案例：查询所有价格不超过5的商品信息，同时加上供应商ID为1001和1002的所有商品信息。

一部分一部分查，先查 所有价格不超过5的商品信息：
select vend_id,prod_id, prod_price from products where prod_price<=5;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
+---------+---------+------------+
4 rows in set (0.00 sec)

再查供应商ID为1001和1002的所有商品信息：
select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
5 rows in set (0.00 sec)

使用UNION组合查询：
select vend_id,prod_id, prod_price from products where prod_price<=5  
union
select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
8 rows in set (0.00 sec)