（基础篇）SQL 连接

什么是连接？

  SQL 中的连接（JOIN）语句用于将数据库中的两个或多个表组合起来。由”连接”生成的集合，可以被保存为表，或者当成表来使用。JOIN 语句的含义是把两张表的属性通过它们的值组合在一起。基于 ANSI 标准的 SQL 列出了五种 JOIN 方式：内连接（INNER），全外连接（FULL OUTER），左外连接（LEFT OUTER），右外连接（RIGHT OUTER）和交叉连接（CROSS）。在特定的情况下，一张表（基本表，视图，或连接表）可以和自身进行连接，成为自连接（self-join）。

连接的类型

  连接可以分为以下类型：

连接类型	自连接	内连接	左(外)连接	右(外)连接	全(外)连接	交叉连接
关键字	无	INNER JOIN	LEFT (OUTER) JOIN	RIGHT (OUTER) JOIN	FULL (OUTER) JOIN	CROSS JOIN

  常用的连接为内连接，左连接，右连接，自连接，一般用不到全外连接和交叉连接。

所需数据表

  为了表达清楚上面各种连接的区别，需要创建 2 张数据表:
  供货商表vendors信息（主键为vend_id）如下图：

供货商表

  商品表products信息(主键为prod_id，外键为供货商主键vend_id)如下图：

商品表

自连接

  假如你在商品表发现某物品（其prod_id为 20005）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。

  第一种解决方案：
  首先找到生产prod_id为 20005 的物品的供应商，然后找出这个供应商生产的其他物品：

# 查询语句
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 20005);

# 查询结果
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| 20005   | MIX 3     |
| 20006   | NFC       |
+---------+-----------+

  上面使用了子查询。内部的 SELECT 语句做 了一个简单的检索，返回生产prod_id为 20005 的物品供应商的vend_id。该 id 作为外部查询 WHERE 子句的条件，以便检索出这个供应商生 产的所有物品。

  但这样太过繁琐，我们可以通过使用自连接，也将得到上面的结果。
  第二种解决方案：

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 = 20005;

内连接

  内连接：通过把一个数据表里的数据行与另一个数据表里的数据行进行匹配来产生结果。
  内连接使用INNER JOIN关键字连接两表，可以显示左表和右表符合连接条件的记录：

SELECT *
FROM vendors AS v INNER  JOIN  products AS p
ON v.vend_id = p.vend_id;

运行结果

  查询语句中：

• *：代表从FROM子句所列出的每一个数据表里选取每一个数据列，与vendors.*,products.*等效。
• ON：与 WHERE等效。

# 查询语句
SELECT p.prod_name,p.prod_price,p.prod_desc,v.vend_name
FROM vendors AS v INNER  JOIN  products AS p
ON v.vend_id = p.vend_id;

# 查询结果
+--------------+------------+--------------+-----------+
| prod_name    | prod_price | prod_desc    | vend_name |
+--------------+------------+--------------+-----------+
| 荣耀9999X    |    5999.00 | 华为手机     | 华为      |
| MAC6666      |    8999.00 | MAC电脑      | 苹果      |
| iphone6s     |    4366.00 | 苹果手机     | 苹果      |
| S8 edge      |    3999.00 | 三星手机     | 三星      |
| MIX 3        |    4236.00 | 小米手机     | 小米      |
| NFC          |     888.00 | 智能手环     | 小米      |
| cloud server |     999.00 | 云服务器     | 华为      |
| sony camera  |    3999.00 | 数码相机     | 索尼      |
| Xperia XZ4   |    4666.00 | 索尼手机     | 索尼      |
+--------------+------------+--------------+-----------+

  如下图：

注意哦：若内连接不添加连接条件，将显示笛卡尔积（见交叉连接），如：

SELECT *
FROM vendors INNER  JOIN  products;

外连接

  内连接只显示在两个数据表里都能找到匹配的数据行。
  外连接除了显示同样的匹配结果，还可把其中一个数据表在另一数据表里未匹配的数据也显示出来。
  外连接又分为：

• 左（外）连接：把左表数据在右表中没有匹配的数据行也显示出来；
• 右（外）连接：把右表数据在左表中没有匹配的数据行也显示出来；

左（外）连接

  显示左表的所有记录，即使在右表中没有匹配的数据行也显示出来。

# 查询语句
SELECT *
FROM vendors AS v LEFT  JOIN  products AS p
ON v.vend_id = p.vend_id

查询结果

  从查询结果可以看到供货商英特尔没有提供商品在商品表中。
  抽象化理解如下图：

右（外）连接

  类似于左（外）连接，关键字为RIGHT JOIN，抽象化理解如下图：

全（外）连接（了解）

  显示左表和右表的并集数据，因为 MySQL 不支持该连接（ SQL Server 支持），所以不再演示，抽象化理解如下图：

交叉连接（笛卡尔积）

  由没有连接条件的表关系返回的结果为笛卡儿积：检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
  如对供货商表和商品表使用交叉连接的 sql 语句及查询结果：

SELECT * 
FROM vendors CROSS JOIN  products

查询结果

  可以看到有许多数据是供货商表和商品表没有的。
  抽象化理解：把这两张表当作一张纸里相交叉的两个圆，数据却居然为整张纸（包括两个圆），如下图：

避免歧义：如何在连接操作中给出数据列的名字

  在SELECT语句里给出的数据列的名字不允许产生歧义，而且必须来自FROM字句的某个数据库。
  若只涉及一个数据表，就不会产生歧义，因为被列出的所有数据列都来自那个数据表。
  若涉及多个数据表，只在一个表中出现的数据列不会产生歧义。但是若某个数据列的名字出现在多个数据表中，在引用这个数据列时必须使用tb1_name1.col_name的语法给它加上一个数据表的名字来表明它来自哪一个数据表。

  有时用数据表的名字进行限定仍不能解决数据列的歧义问题。
  比如自连接操作是在查询命令里多次用到同一个数据表，用数据表的名字来限定数据列没有用。此时应使用表别名，如：

SELECt t1.name,t2.age 
FROM table AS t1 INNER JOIN table AS t2 
WHERE t1.age > t2.age;

参考

• Ben Forta. MySQL 必知必会 [M]. 人民邮电出版社, 2009
• Paul DuBois. MySQL 技术内幕 [M]. 人民邮电出版社, 2011

文章信息

时间	说明
2019-02-09	初稿