SQL 联结表

一、联结：

  • SQL最强大的功能之一就是能在数据查询的执行中联结(join)表。
  • 联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法学习SQL的极为重要的部分。

1.1：关系表：

  • 关系表的设计：
  • 关系表的设计就是要把信息分解成多个表，一类数据一个表。名表通过某些共同的值互相关联(所以才叫关系数据库)。
  • 关系表的优点：
  • 可伸缩性要比非关系数据库要好。
  • 可伸缩：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好。

1.2：为什么使用联结：

  • 上面所述，将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好。但这些好处是有代价的。
  • 如果数据存储再多个表中，怎样用一条SELECT语句就检索出数据呢？
  • 答案是使用联结。
  • 联结是一种机制，用来再一条SELECT语句中关联表，因此称为联结。
  • 使用特殊语法，可以联结多个表返回一组输出，联结再运行时关联表中正确的行。
  • 说明：使用交互式DBMS工具
  • 要理解联结不是物理实体。它实际的数据库表中并不存在。DBMS会根据需要建立联结，它再查询执行期间一直存在。

二、创建联结：

  • 首先看Vendors表和Products表有哪些数据：
mysql> SELECT * FROM Vendors;
+---------+-----------+--------------+------------+----------+--------------+
| vend_id | vend_name | vend_address | vend_city  | vend_zip | vend_country |
+---------+-----------+--------------+------------+----------+--------------+
| BRE02   | Bear Empo | 500 Park Str | Anytown    | 44333    | USA          |
| BRS01   | Bears R U | 123 Main Str | Bear Town  | 44444    | USA          |
| DLL01   | Doll Hous | 555 High Str | Dollsville | 99999    | USA          |
| FNG01   | Fun and G | 42 Galaxy Ro | London     | N16 6PS  | England      |
| FRB01   | Furball I | 1000 5th Ave | New York   | 11111    | USA          |
| JTS01   | Jouets et | 1 Rue Amusem | Paris      | 45678    | France       |
+---------+-----------+--------------+------------+----------+--------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM products;
+---------+---------+------------+------------+--------------------------------+
| prod_id | vend_id | prod_name  | prod_price | prod_desc                      |
+---------+---------+------------+------------+--------------------------------+
| BNBG01  | DLL01   | Fish bean  |       3.49 | Fish bean bean bag worms with  |
| BNBG02  | DLL01   | Bird bean  |       3.49 | Bird beanincluded              |
| BNBG03  | DLL01   | Rabbit bey |       3.49 | Rabbit bebean bag carrots      |
| BR01    | BRS01   | 8 inch te  |       5.99 | 8 inch tep and jacket          |
| BR02    | BRS01   | 12 inch t  |       8.99 | 12 inch tap and jacket         |
| BR03    | BRS01   | 18 inch t  |      11.99 | 18 inch tap and jacket         |
| RGAN01  | DLL01   | Raggedy A  |       4.99 | 18 inch R                      |
| RYL01   | FNG01   | King doll  |       9.49 | 12 inch kments and crown       |
| RYL02   | FNG01   | Queen dol  |       9.49 | 12 inch qrments and crown      |
+---------+---------+------------+------------+---------------------------------
  • 创建联结非常简单，指定要联结的所有表以及关联它们的方式：
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products 
WHERE Vendors.vend_id = Products.vend_id;
+-----------------+---------------------+------------+
| 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 |
+-----------------+---------------------+------------+
9 rows in set (0.00 sec)
  • SELECT 语句与前面所有语句一样指定要检索的列。
  • 这里最大的差别是指定的两列(prod_name和prod_price)再一个表中，而第三列(vend_name)再另一个表中。
  • 现在来看FROM子句。与以前的SELECT语句不一样，这条语句的FROM子句列出了两个表的名字。
  • 这两个表用WHERE子句正确地联结，WHERE子句指示DBMS将Vend_id与Products表中的vend_id匹配起来。
  • 这里需要这种完全限定列名，如果只给出vend_id，DBMS就不知道指的哪一个(每个表中有一个)。
  • 注意：完全限定列名
  • 再引用的列可能出现歧义时，必须使用完全限定列名(用一个句点分隔表名和列名)。如果引用一个没有用表名限制的具有歧义的列名，大多数DBMS会返回错误。

2.1：WHERE子句的重要性：

  • 使用WHERE子句建立联结关系似乎有点奇怪，但实际上是有个很充分的理由。
  • 再一条SELECT语句中联结几个表时，相应的关系是再运行中构成的。再数据库表的定义中没有指示DBMS如何对表进行联结的内容。
  • 再联结两个表时，实际要做的将第一表中的每一行与第二个表中的每一行配对。
  • WHERE子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行。
  • 没有WHERE子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起。
  • 笛卡儿积：
  • 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行数目将是第一个表中的行数乘以第二个表中的行数。
  • 返回笛卡儿积的联结，也称为叉联结。
SELECT vend_name, prod_name, prod_price FROM Vendors, Products;
+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Bear Emporium   | Fish bean bag toy   |       3.49 |
| Bears R Us      | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Fun and Games   | Fish bean bag toy   |       3.49 |
| Furball Inc.    | Fish bean bag toy   |       3.49 |
| Jouets et ours  | Fish bean bag toy   |       3.49 |
| Bear Emporium   | Bird bean bag toy   |       3.49 |
| Bears R Us      | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Fun and Games   | Bird bean bag toy   |       3.49 |
| Furball Inc.    | Bird bean bag toy   |       3.49 |
| Jouets et ours  | Bird bean bag toy   |       3.49 |
| Bear Emporium   | Rabbit bean bag toy |       3.49 |
| Bears R Us      | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
| Fun and Games   | Rabbit bean bag toy |       3.49 |
| Furball Inc.    | Rabbit bean bag toy |       3.49 |
| Jouets et ours  | Rabbit bean bag toy |       3.49 |
| Bear Emporium   | 8 inch teddy bear   |       5.99 |
| Bears R Us      | 8 inch teddy bear   |       5.99 |
| Doll House Inc. | 8 inch teddy bear   |       5.99 |
| Fun and Games   | 8 inch teddy bear   |       5.99 |
| Furball Inc.    | 8 inch teddy bear   |       5.99 |
| Jouets et ours  | 8 inch teddy bear   |       5.99 |
| Bear Emporium   | 12 inch teddy bear  |       8.99 |
| Bears R Us      | 12 inch teddy bear  |       8.99 |
| Doll House Inc. | 12 inch teddy bear  |       8.99 |
| Fun and Games   | 12 inch teddy bear  |       8.99 |
| Furball Inc.    | 12 inch teddy bear  |       8.99 |
| Jouets et ours  | 12 inch teddy bear  |       8.99 |
| Bear Emporium   | 18 inch teddy bear  |      11.99 |
| Bears R Us      | 18 inch teddy bear  |      11.99 |
| Doll House Inc. | 18 inch teddy bear  |      11.99 |
| Fun and Games   | 18 inch teddy bear  |      11.99 |
| Furball Inc.    | 18 inch teddy bear  |      11.99 |
| Jouets et ours  | 18 inch teddy bear  |      11.99 |
| Bear Emporium   | Raggedy Ann         |       4.99 |
| Bears R Us      | Raggedy Ann         |       4.99 |
| Doll House Inc. | Raggedy Ann         |       4.99 |
| Fun and Games   | Raggedy Ann         |       4.99 |
| Furball Inc.    | Raggedy Ann         |       4.99 |
| Jouets et ours  | Raggedy Ann         |       4.99 |
| Bear Emporium   | King doll           |       9.49 |
| Bears R Us      | King doll           |       9.49 |
| Doll House Inc. | King doll           |       9.49 |
| Fun and Games   | King doll           |       9.49 |
| Furball Inc.    | King doll           |       9.49 |
| Jouets et ours  | King doll           |       9.49 |
| Bear Emporium   | Queen doll          |       9.49 |
| Bears R Us      | Queen doll          |       9.49 |
| Doll House Inc. | Queen doll          |       9.49 |
| Fun and Games   | Queen doll          |       9.49 |
| Furball Inc.    | Queen doll          |       9.49 |
| Jouets et ours  | Queen doll          |       9.49 |
+-----------------+---------------------+------------+
54 rows in set (0.00 sec)
  • 从上面的输出可以看到，相应的笛卡儿积不是我们想要的。这里返回的数据用每个供应商匹配了每个产品，包括了供应商不正确的产品。
  • 注意：不要忘了WHERE子句
  • 要保证所有联结都有WHERE子句，否则DBMS将返回比想要的数据多得多的数据。

2.2：内联结：

  • 目前为止使用的联结称为等值联结，它基于两个表之间的相等测试。这种联结也成为内联结(inner join)
  • 首先看一下Vendors表和Products表都有哪些数据？
SELECT * FROM Products;
+---------+---------+------------+------------+--------------------------------+
| prod_id | vend_id | prod_name  | prod_price | prod_desc                      |
+---------+---------+------------+------------+--------------------------------+
| BNBG01  | DLL01   | Fish bean  |       3.49 | Fish bean bean bag worms with  |
| BNBG02  | DLL01   | Bird bean  |       3.49 | Bird beanincluded              |
| BNBG03  | DLL01   | Rabbit bey |       3.49 | Rabbit bebean bag carrots      |
| BR01    | BRS01   | 8 inch te  |       5.99 | 8 inch tep and jacket          |
| BR02    | BRS01   | 12 inch t  |       8.99 | 12 inch tap and jacket         |
| BR03    | BRS01   | 18 inch t  |      11.99 | 18 inch tap and jacket         |
| RGAN01  | DLL01   | Raggedy A  |       4.99 | 18 inch R                      |
| RYL01   | FNG01   | King doll  |       9.49 | 12 inch kments and crown       |
| RYL02   | FNG01   | Queen dol  |       9.49 | 12 inch qrments and crown      |
+---------+---------+------------+------------+---------------------------------
mysql> SELECT * FROM Vendors;
+---------+-----------+--------------+------------+----------+--------------+
| vend_id | vend_name | vend_address | vend_city  | vend_zip | vend_country |
+---------+-----------+--------------+------------+----------+--------------+
| BRE02   | Bear Empo | 500 Park Str | Anytown    | 44333    | USA          |
| BRS01   | Bears R U | 123 Main Str | Bear Town  | 44444    | USA          |
| DLL01   | Doll Hous | 555 High Str | Dollsville | 99999    | USA          |
| FNG01   | Fun and G | 42 Galaxy Ro | London     | N16 6PS  | England      |
| FRB01   | Furball I | 1000 5th Ave | New York   | 11111    | USA          |
| JTS01   | Jouets et | 1 Rue Amusem | Paris      | 45678    | France       |
+---------+-----------+--------------+------------+----------+--------------+
6 rows in set (0.00 sec)
  • 下面使用SELECT语句返回与前面栗子完全相同的数据：
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 |
+-----------------+---------------------+------------+
| 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 |
+-----------------+---------------------+------------+
9 rows in set (0.00 sec)
  • 语句中的SELECT与前面SELECT语句相同，但FROM子句不同。
  • 两个表之间的关系是以INNER JOIN指定的部分FROM子句。联结条件用特定的ON子句而不是WHERE子句给出
  • 传递给ON的实际条件与传递给WHERE的相同
  • 提示：应该首选INNER JOIN 语法
  • SQL规范首选INNER JOIN语法，之前使用的简单的等值语法。SQL语言纯正论者是用鄙视的眼光看待简单语法的。还是建议大家使用INNER JOIN

三、联结多个表：

  • SQL不限制一条SELECT语句中可以联结的表的数目。列出所有表，然后定义表之间的关系。
  • 下面举个栗子，多个表之间的关联，首选使用等值语法进行关联：
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;
+---------------------+-----------------+------------+----------+
| prod_name           | vend_name       | prod_price | quantity |
+---------------------+-----------------+------------+----------+
| 18 inch teddy bear  | Bears R Us      |      11.99 |       50 |
| Fish bean bag toy   | Doll House Inc. |       3.49 |      100 |
| Bird bean bag toy   | Doll House Inc. |       3.49 |      100 |
| Rabbit bean bag toy | Doll House Inc. |       3.49 |      100 |
| Raggedy Ann         | Doll House Inc. |       4.99 |       50 |
+---------------------+-----------------+------------+----------+
5 rows in set (0.01 sec)
  • 分析一下这个栗子：
  • 显示订单20007中的物品。
  • 订单物品存储再OrderItems表中。
  • 每个产品按其产品ID存储，它引用Products表中的产品。
  • 这些产品通过供应商ID联结到Vendors表中相应的供应商，供应商ID存储在每个产品的记录中。
  • 这里的FROM子句列出三个表，WHERE子句定义这两个联结条件，而第三个联结条件用来过滤出订单20007中的物品。
  • 注意：性能考虑
  • DBMS再运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。