SQL必知会（二）-SQL查询篇（11）-联结表

第12课、联结表

联结表
使用联结：之前的 SELECT 查询某些列，只是针对一张表进行检索的。（即 FROM 表名）。但是如果用户想要使用 SELECT 对存储在不同表中的某些列时，则需要使用联结表的方式来实现。

例子中有建立两个表：

• Vendors 表：存储供应商信息，每个供应商具有唯一的标识，为主键。
• Products 表：存储产品信息。还存储供应商 ID（Vendors 表的主键）。

如果既想要在 Vendors 表中查询 vend_name ，又想要在 Products 表中查询 prod_name 和 pro_price，则使用联结表的方式来处理。

需求：如果两个表中存在相同的供应商ID，则输出其所有的供应商名称，产品名成，产品价格。

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
1
2
3

输出结果：

vend_name       	 prod_name	           prod_price
Doll House Inc.     Fish bean bag toy          3.49
Doll House Inc.     Bird bean bag toy          3.49
Doll House Inc.     Rabbit bean bag toy      3.49
Bears R Us            8 inch teddy bear           5.99
Bears R Us            12 inch teddy bear         8.99
Bears R Us            18 inch teddy bear         11.99
Doll House Inc.      Raggedy Ann                4.99
Fun and Games     King doll                       9.49
Fun and Games     Queen doll                    9.49
1
2
3
4
5
6
7
8
9
10

假如执行如下内容：

-- 去掉了 WHERE 子句判断条件：
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;
-- 第一个表中的每一行将与第二表中的每一行配对，而不管它们是否能配在一起。
-- 由于查询的列没有判断共同字段值的条件，将会以输出各自的结果相结合在一起。
-- A 表中有2个列a值，B 表中有3个列b值，则输出结果有可能为：
a   b
------
a1  b1
a1  b2
a1  b3
a2  b1
a2  b2
a2  b3

--WHERE 有共同列的判断条件，才能在两张表中一一配对。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

内联结是查找出同时存在于两张表中的数据，内联结用英文单词表示是inner join。 也成为等值联结。

-- 语法1 和语法2，执行结果一致
--语法1
SELECT vend_name, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON 
Vendors.vend_id = Products.vend_id;

-- 语法2
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

1
2
3
4
5
6
7
8
9
10
11
12

SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
1
2
3
4
5

需求：检索订购物品 RGAN01 的所有顾客。

-- 在 之前子查询中使用：
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'));

-- 在联结表中使用：
SELECT cust_name,cust_contact
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

输出结果：

cust_name	                     cust_contact
Fun4All                          Denise L. Stephens            
The Toy Store                    Kim Howard                                        
1
2
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;
	
-- 目前尝试了一遍，发现不可以（不知道能不能解决这个问题）：
-- 我的转成后是这样的：
SELECT cust_name, cust_state,COUNT(Customers.cust_id) as orders
FROM Customers,Orders
WHERE Customers.cust_id = Orders.cust_id
group by  cust_name,cust_state;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

总结

联结表

SELECT 表1列, 表2列...
FROM 表1, 表2...
WHERE 有共同列名的过滤条件;

另外语法：
SELECT 表1列, 表2列...
FROM 表1
INNER JOIN 表2.. ON 
有共同列名的过滤条件;

1
2
3
4
5
6
7
8
9
10

联结表的性能
联结的每一张表，都需要耗费相应的资源。联结的表越多，性能就越下降。