但是，这些操作无法更改列中的更改。尽管可以使用函数或CASE表达式等列操作来增加列数，但它们仍然只能从表中提供的基本信息列中获取一些“扩展列”，这本质上并不能提供更多的信息。如果我们想从多个表中获取信息，比如我们想查出一家商店里的服装产品的名称、数量和价格，我们就必须分别从ShopProduct表和Product表中获取。获取信息。

注： 到目前为止，文章中的示例（关联子查询除外）基本上都是从一张表中选择数据，但实际上，期望的数据往往分散在不同的表中。在这种情况下，您需要使用链接。之前学习相关子查询时，我们发现使用相关子查询也可以从其他表中获取信息，但是联接更适合从多个表中获取信息。

JOIN是利用一定的关联条件（通常使用等式谓词'='）从其他表中添加列，进行“添加列”的集合操作。可以说，连接是SQL查询的核心操作。掌握联接，可以从两个甚至更多表中获取列，将过去过于复杂的查询（例如相关子查询）简化为更易读的形式，并执行一些更复杂的查询。

SQL中连接的分类方法有很多种。这里我们用内部联系和外部联系最基本的分类方法来分别进行解释。

1.2.1 内连结(INNER JOIN)

内部链接的语法格式为：

-- 内部链接FROM tb_1 INNER JOIN tb_2 ON 条件INNER 关键字表示使用内部链接。至于内部链接的含义，暂时无需详细研究。比如还是同样的问题：

查询某店铺服装商品名称、数量、价格等信息。

我们进一步澄清这个问题：

查找东京商店服装产品的产品名称、产品价格、产品类型和产品数量信息。

1.2.1.1 使用内连结从两个表获取信息

我们先分别观察涉及到的表。 Product表存储产品编号、产品名称、产品类型等信息。该表可以提供衣服类型的详细信息，但不能提供商店信息。

接下来我们观察ShopProduct 表，其中包含商店编号和名称、商店的产品编号和数量。但是，如果您想获取产品类型、名称和售价等信息，则必须使用Product 表。

所以问题的关键是找到一个类似于‘axis’或‘bridge’的公共列，并用这个列来连接两个表。这就是串联操作的作用。

我们来比较一下上面两个表。我们可以发现，产品编号列是一个公共列，那么很自然地用这个产品编号列作为“桥梁”来连接Product和ShopProduct这两个表。

注： 从思维上来说，相关子查询更像是Excel中经常使用的vlookup函数。 以A表为主表，然后根据A表中相关列每一行的值，一一传输到B表中相关列。查找具有相等值的行。当数据量较小时，这种方法不会有任何性能问题，但是当数据量较大时，这种方法会造成较大的计算开销。 对于外部查询返回的每一行数据，都会发送到内部子查询，传递相关列的值，然后内部子查询根据传入的值执行查询并返回其查询结果。这使得例如外层主查询的返回结果有10000行，那么子查询就会被执行一万次，这会带来非常可怕的时间消耗。

分解以上问题：

首先查出每个店铺的店铺号、店铺名称、产品编号、产品名称、产品类别、产品售价、产品数量信息。

根据内连接的语法，在FROM子句中使用INNER JOIN连接两个表，并指定ON子句的连接条件为ShopProduct.product_id=Product.product_id，将得到如下查询语句：

SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.product_type ,P.sale_price ,SP.quantity FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id=P.product_id;在上面的查询中，我们分别为两个表指定了简单的别名。这种操作在使用连接时很常见。使用别名会在编写查询时节省我们大量的打字工作，更重要的是，它会使查询语句可读。看起来更加简洁。上述查询将得到如下结果：

观察查询结果，我们发现该结果中的列已经包含了我们需要的所有信息。

关于内部链接，需要注意以下三点：

第1 点： 执行联接时，您需要在FROM 子句中使用多个表。

之前的FROM 子句中只有一张表，但这次我们使用了ShopProduct 和Product 两个表，并使用关键字INNER JOIN 将两个表连接在一起：

FROM ShopProduct AS SP INNER JOIN Product AS P 要点2 : 必须使用ON 子句指定连接条件。

ON 子句在执行内部联接时必不可少（您可以尝试一下如果删除上述查询的ON 子句会发生什么情况）。

ON 子句专门用于指定连接条件。在上面查询中的ON 之后，我们指定用于连接两个表的列和比较条件。基本上可以起到和WHERE一样的过滤作用。我们将在本章的结论部分进一步探讨这个主题。

要点三： SELECT 子句中的列最好采用表名.列名的格式。

当两个表的列除了用于关联的列之外没有同名的列时，就不需要写表名了，但是表名可以让我们以后随时读取查询代码。查看每一列来自哪个表可以节省我们很多时间。

但如果两个表还有其他同名列，则必须使用上述格式选择列名，否则查询语句会报错。

回到上面查询所回答的问题。通过观察上面查询的结果，我们发现这个结果非常接近我们查找东京门店服装商品基本信息的目标。接下来，我们只需要将查询结果看成一张表，并在其中添加一个WHERE子句来指定过滤条件。

1.2.1.2 结合 WHERE 子句使用内连结

如果使用内连接时需要使用WHERE子句来过滤搜索结果，则需要将WHERE子句写在ON子句之后。

例如，对于上面的查询问题，我们可以在前面的查询的基础上添加一个WHERE条件。

添加WHERE 子句的方法有多种，我们从最简单的一种开始。

添加WEHRE子句的第一种方法是将上面的查询作为子查询，将其封装在括号中，然后在外层查询中添加过滤条件。

SELECT * FROM (--第一步查询的结果SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.product_type ,P.sale_price ,SP.quantity FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id=P.product_id) AS STEP1 WHERE shop_name='东京' AND Product_type='服装' ;子查询的结果实际上是一张表，只是一张虚拟表，它并不真正存在于数据库中，只是对数据库中其他表进行过滤、聚合等查询操作后得到的一个“视图”。这种写法可以清楚地区分每个操作步骤，当你不太熟悉SQL查询的每个子句的执行顺序时，可以为你提供帮助。

但实际上，如果你很清楚WHERE子句会在FROM子句之后执行，也就是说，直到你完成了INNER JOIN . ON并获得了一张新表之后，才会执行WHERE子句，那么你会得到标准的写法：

SELECT SP.shop_id、SP.shop_name、SP.product_id、P.product_name、P.product_type、P.sale_price、SP.quantity FROM ShopProduct AS SP INNER JOIN 产品AS P ON SP.product_id=P.product_id WHERE SP.shop_name='Tokyo' AND P.product_type='Clothes' ;我们首先给出上面查询：的执行顺序

FROM 子句-WHERE 子句-SELECT 子句

也就是说，先根据连接列将两张表连接起来，得到一张新表。然后WHERE 子句根据两个条件过滤这个新表的行。最后，SELECT 子句选择我们需要的那些。列表。

另外，一种不太常见的做法是将WHERE子句中的条件直接添加到ON子句中。在这种情况下，最好在ON 子句后面使用括号将连接条件和过滤条件括起来。

SELECT SP.shop_id 、SP.shop_name 、SP.product_id 、P.product_name 、P.product_type 、P.sale_price 、SP.quantity FROM ShopProduct AS SP INNER JOIN Product AS P ON (SP.product_id=P.product_id AND SP.shop_name='东京' AND P.product_type='衣服') ;不过，上述在ON子句中编写过滤条件和连接条件的方式不太容易阅读，不建议大家使用。另外，先连接后过滤的标准写法的执行顺序是两个完整的表连接后过滤。如果要连接多个表，或者需要做的过滤比较复杂，那么在编写SQL查询时就会感觉不舒服。就比较费力了。当内连接与WHERE子句结合使用时，我们还可以改变任务顺序，使用任务分解的方法。首先使用WHERE分别过滤两个表，然后连接上面的两个子查询。

SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.product_type ,P.sale_price ,SP.quantity FROM (-- 子查询1: 从ShopProduct 表中筛选出东京商店信息SELECT * FROM ShopProduct WHERE shop_name='Tokyo' ) AS SP INNER JOIN -- 子查询2: 从Product 表中过滤出有关服装产品的信息(SELECT * FROM Product WHERE Product_type='Clothes') AS P ON SP.product_id=P.product_id;首先，分别在两个表中进行过滤，将复杂的过滤条件按表拆分，然后将过滤结果（作为表）连接起来，避免编写复杂的过滤条件。所以，这种看似复杂的写法，其实就是整体的逻辑。相反，这是非常清楚的。编写查询时，必须首先以最容易理解的方式编写它。先解决问题，然后再考虑优化问题。

练习：

查询各店铺服装商品名称、价格等信息。我希望得到以下结果：

--参考答案1--不要使用子查询SELECT SP.shop_id,SP.shop_name,SP.product_id ,P.product_name, P.product_type, P.purchase_price FROM shopproduct AS SP INNER JOIN Product AS P ON SP.product_id=P .product_id WHERE P.product_type='Clothes';--参考答案2--使用子查询SELECT SP.shop_id, SP.shop_name, SP.product_id ,P.product_name, P.product_type, P.purchase_price FROM shopproduct AS SP INNER JOIN -- 从Product 表中查找服装产品信息(SELECT Product_id, Product_name, Product_type,purchase_price FROM Product WHERE Product_type='Clothes') AS P ON SP.product_id=P.product_id;上面第二种写法虽然包含了子查询，代码行数较多，但由于每一层的目的都很明确，所以更适合阅读，而且在外部连接的情况下，也可以避免错误使用WHERE子句导致外部连接失败的问题。请参阅本文后面的“在WHERE 子句中使用外连接”部分的相关示例。

练习：

使用连接两个子查询和分别不使用子查询的方法，我们可以找到东京商店中价格低于2000的商品信息。我们希望得到以下结果。

-- 参考答案-- 不要使用子查询SELECT SP.*, P.* FROM shopproduct AS SP INNER JOIN Product AS P ON SP.product_id=P.product_id WHERE shop_id='000A' AND sale_price 2000;

1.1.3 结合 GROUP BY 子句使用内连结

与GROUP 组合BY 子句使用内连接，需要根据分组列所在的表进行不同的处理。

最简单的情况是在内连接之前使用GROUP BY 子句。

但是，如果分组列和被聚合的列不在同一个表中，并且都没有用于连接两个表，则只能先连接，然后聚合。

练习：

每家商店中价格最高的商品的价格是多少？

-- 参考答案SELECT SP.shop_id ,SP.shop_name ,MAX(P.sale_price) AS max_price FROM shopproduct AS SP INNER JOIN Product AS P ON SP.product_id=P.product_id GROUP BY SP.shop_id,SP.shop_name 问题：

上面的查询获取了每个产品的销量最高的产品，但是它不知道哪个产品是销量最高的产品。如何获取每个商店中销量最高的产品的名称和售价？

Note : 解决这个问题的一个简单方法是使用下一章中的窗口函数。当然，你也可以利用我们学到的其他知识来实现。例如，找到每个商店中最畅销商品的价格后，使用此价格与“产品”列链接，但当价格不唯一时，这可能会导致问题。

1.1.4 自连结(SELF JOIN)

之前的内部链接连接了两个不同的表。但实际上，表也可以与其自身连接。这种连接称为自连接。需要注意的是，自连接并不是区别于内部连接和外部连接的第三种连接类型。自联接可以是外部连接，也可以是外部连接。它是内部链接，是区别于内部链接和外部链接的另一种链接分类方法。

1.1.5 内连结与关联子查询

回忆一下第5章第3节中相关子查询中的问题：查找每个产品类别中售价高于该类型产品平均售价的产品。当时我们使用相关子查询来实现这一点。

SELECT 产品类型、产品名称、销售价格FROM 产品AS P1 WHERE sale_price (SELECT AVG(sale_price) FROM 产品AS P2 WHERE P1.product_type=P2.product_type GROUP BY Product_type);使用内部链接也可以解决这个问题： 首先，使用GROUP BY按钮产品类别分类计算每个类别商品的平均价格。

SELECT Product_type ,AVG(sale_price) AS avg_price FROM Product GROUP BY Product_type;接下来，根据product_type（产品类型）将上述查询内部连接到表Product。

SELECT P1.product_id ,P1.product_name ,P1.product_type ,P1.sale_price ,P2.avg_price FROM Product AS P1 INNER JOIN (SELECT Product_type,AVG(sale_price) AS avg_price FROM Product GROUP BY Product_type) AS P2 ON P1.product_type=P2 .产品类型；最后添加一个WHERE子句来查找那些售价高于该类产品平均价格的产品。完整代码如下：

SELECT P1.product_id ,P1.product_name ,P1.product_type ,P1.sale_price ,P2.avg_price FROM Product AS P1 INNER JOIN (SELECT Product_type,AVG(sale_price) AS avg_price FROM Product GROUP BY Product_type) AS P2 ON P1.product_type=P2 .product_type WHERE P1.sale_price P2.avg_price;仅从代码量来看，上述方法似乎比相关子查询更复杂，但这并不意味着这些代码更难理解。通过上面的分析，我们不难发现上面代码的逻辑其实是比较符合我们的思维的，所以虽然看起来很复杂，但是思路其实是比较清晰的。

1.1.6 自然连结(NATURAL JOIN)

自然连接不是不同于内部连接和外部连接的第三种连接类型。它实际上是内部联接的一种特殊情况——当两个表自然联接时，它们将根据两个表中包含的列进行链接。执行等效内部连接的名称。此时无需使用ON来指定连接条件。

SELECT * FROM shopproduct NATURAL JOIN Product 上述查询得到的结果会将两个表的公共列（这里是product_id，可以有多个公共列）放在第一列，然后将表中的列添加到两个表的顺序。按顺序列出两个表中的其他列。

练习：

尝试编写与上述自然链接等效的内部链接。

-- 参考答案SELECT SP.product_id,SP.shop_id,SP.shop_name,SP.quantity ,P.product_name,P.product_type,P.sale_price ,P.purchase_price,P.regist_date FROM shopproduct AS SP INNER JOIN Product AS P ON SP.product_id=P.product_id 还可以使用自然连接找到两个表或子查询的公共部分。例如课本中的7-1选择表的公共部分--INTERSECT部分题：找到表Product和表Product2的公共部分，也可以使用自然链接来实现：

从产品中选择* 自然连接产品2

这个结果与书上给出的结果并不一致。没有运动T恤。这是因为运动T恤的register_date字段为空。建立自然链接时，将比较来自Product 和Product2 的运动T 恤的数据行。当时，我们实际上是逐个字段执行等效连接。回想一下我们在3.4.4节中学到的缺失值比较方法ISNULL，IS NOT NULL。我们可以知道，两个缺失值通过等号进行比较。结果不真实。连接将仅返回连接条件返回true 的那些行。

如果我们修改查询语句：

SELECT * FROM (SELECT Product_id, Product_name FROM Product) AS A NATURAL JOIN (SELECT Product_id, Product_name FROM Product2) AS B;那么你可以得到正确的结果：

1.1.7 使用连结求交集

从上一节关于表加减法的内容我们知道，MySQL 8.0中没有交集运算。我们使用并和差来实现交集。现在我们已经了解了联接，让我们尝试使用联接。实现相交操作。

练习： 使用内联接查找Product 表和Product2 表的交集。

SELECT P1.* FROM Product AS P1 INNER JOIN Product2 AS P2 ON (P1.product_id=P2.product_id AND P1.product_name=P2.product_name AND P1.product_type=P2.product_type AND P1.sale_price=P2.sale_price AND P1.regist_date=P2.regist_date) 并得到以下结果

请注意，上述结果与P230 的结果不一致- 缺少行product_id='0003'。观察源表数据，我们可以发现这个缺失行的register_date是一个缺失值。回想一下第6 章中提到的IS NULL 谓词，我们了解到这是因为缺失值无法使用等号进行比较。

如果我们只使用product_id来链接：

SELECT P1.* FROM Product AS P1 INNER JOIN Product2 AS P2 ON P1.product_id=P2.product_id 查询结果：

这次是一致的。

1.2 外连结(OUTER JOIN)

内连接将丢弃两个表中不满足ON 条件的行。与内连接相反的是外连接。外连接将根据外连接的类型选择性地保留不匹配的行。

根据保留行所在的表，外部联接有三种形式：左联接、右联接和全外部联接。

左链接保险

存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。 三种外连结的对应语法分别为: -- 左连结 FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>-- 右连结 FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>-- 全外连结FROM <tb_1> FULL OUTER JOIN <tb_2> ON <condition(s)>

1.2.1 左连结与右连结

由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别.接下来我们先以左连结为例进行学习. 所有的内容在调换两个表的前后位置, 并将左连结改为右连结之后, 都能得到相同的结果. 稍后再介绍全外连结的概念。

1.2.2 使用左连结从两个表获取信息

如果你仔细观察过将 shopproduct 和 product 进行内连结前后的结果的话, 你就会发现, product 表中有两种商品并未在内连结的结果里, 就是说, 这两种商品并未在任何商店有售(这通常意味着比较重要的业务信息, 例如, 这两种商品在所有商店都处于缺货状态, 需要及时补货). 现在, 让我们先把之前内连结的 SELECT 语句转换为左连结试试看吧。 练习题: 统计每种商品分别在哪些商店有售, 需要包括那些在每个商店都没货的商品。 使用左连结的代码如下(注意区别于书上的右连结): SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.sale_price FROM Product AS P LEFT OUTER JOIN ShopProduct AS SP ON SP.product_id = P.product_id;上述查询得到的检索结果如下(由于并未使用 ORDER BY 子句指定顺序,你执行上述代码得到的结果可能顺序与下图不同): ​ 我们观察上述结果可以发现, 有两种商品: 高压锅和圆珠笔, 在所有商店都没有销售. 由于我们在 SELECT 子句选择列的显示顺序以及未对结果进行排序的原因, 这个事实需要你仔细地进行观察。 ● 外连结要点 1: 选取出单张表中全部的信息 与内连结的结果相比,不同点显而易见,那就是结果的行数不一样.内连结的结果中有 13 条记录,而外连结的结果中有 15 条记录,增加的 2 条记录到底是什么呢?这正是外连结的关键点. 多出的 2 条记录是高压锅和圆珠笔,这 2 条记录在 ShopProduct 表中并不存在,也就是说,这 2 种商品在任何商店中都没有销售.由于内连结只能选取出同时存在于两张表中的数据,因此只在 Product 表中存在的 2 种商品并没有出现在结果之中.相反,对于外连结来说,只要数据存在于某一张表当中,就能够读取出来.在实际的业务中,例如想要生成固定行数的单据时,就需要使用外连结.如果使用内连结的话,根据 SELECT 语句执行时商店库存状况的不同,结果的行数也会发生改变,生成的单据的版式也会受到影响,而使用外连结能够得到固定行数的结果.虽说如此,那些表中不存在的信息我们还是无法得到,结果中高压锅和圆珠笔的商店编号和商店名称都是 NULL （具体信息大家都不知道,真是无可奈何）.外连结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在（在原表之外）的信息”.相反,只包含表内信息的连结也就被称为内连结了。 ● 外连结要点 2：使用 LEFT、RIGHT 来指定主表. 外连结还有一点非常重要,那就是要把哪张表作为主表.最终的结果中会包含主表内所有的数据.指定主表的关键字是 LEFT 和 RIGHT.顾名思义,使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表.代码清单 7-11 中使用了 RIGHT ,因此,右侧的表,也就是 Product 表是主表.我们还可以像代码清单 7-12 这样进行改写,意思完全相同.这样你可能会困惑，到底应该使用 LEFT 还是 RIGHT？其实它们的功能没有任何区别,使用哪一个都可以.通常使用 LEFT 的情况会多一些,但也并没有非使用这个不可的理由,使用 RIGHT 也没有问题。 通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT,则更换为 LEFT), 两种方式会到完全相同的结果。

1.2.3 结合 WHERE 子句使用左连结

上一小节我们学到了外连结的基础用法, 并且在上一节也学习了结合WHERE子句使用内连结的方法, 但在结合WHERE子句使用外连结时, 由于外连结的结果很可能与内连结的结果不一样, 会包含那些主表中无法匹配到的行, 并用缺失值填写另一表中的列, 由于这些行的存在, 因此在外连结时使用WHERE子句, 情况会有些不一样. 我们来看一个例子: 练习题: 使用外连结从ShopProduct表和Product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果。 ​ 注意高压锅和圆珠笔两种商品在所有商店都无货, 所以也应该包括在内。 按照"结合WHERE子句使用内连结"的思路, 我们很自然会写出如下代码 SELECT P.product_id ,P.product_name ,P.sale_price ,SP.shop_id ,SP.shop_name ,SP.quantity FROM Product AS P LEFT OUTER JOIN ShopProduct AS SP ON SP.product_id = P.product_id WHERE quantity< 50然而不幸的是, 得到的却是如下的结果: ​ 观察发现, 少了在所有商店都无货的高压锅和圆珠笔. 聪明的你可能很容易想到，在WHERE过滤条件中增加 OR quantity IS NULL 的条件, 便可以得到预期的结果。然而在真实的查询环境中, 由于数据量大且数据质量并非设想的那样"干净", 我们并不能容易地意识到缺失值等问题数据的存在, 因此,还是让我们想一下如何改写我们的查询以使得它能够适应更复杂的真实数据的情形吧。 联系到我们已经掌握了的SQL查询的执行顺序(FROM->WHERE->SELECT),我们发现, 问题可能出在筛选条件上, 因为在进行完外连结后才会执行WHERE子句, 因此那些主表中无法被匹配到的行就被WHERE条件筛选掉了。 明白了这一点, 我们就可以试着把WHERE子句挪到外连结之前进行: 先写个子查询,用来从ShopProduct表中筛选quantity<50的商品, 然后再把这个子查询和主表连结起来。

我们把上述思路写成SQL查询语句: SELECT P.product_id ,P.product_name ,P.sale_price ,SP.shop_id ,SP.shop_name ,SP.quantity FROM Product AS P LEFT OUTER JOIN-- 先筛选quantity<50的商品 (SELECT * FROM ShopProduct WHERE quantity < 50 ) AS SP ON SP.product_id = P.product_id得到的结果如下: ​

1.2.4 在 MySQL 中实现全外连结

有了对左连结和右连结的了解, 就不难理解全外连结的含义了. 全外连结本质上就是对左表和右表的所有行都予以保留, 能用 ON 关联到的就把左表和右表的内容在一行内显示, 不能被关联到的就分别显示, 然后把多余的列用缺失值填充。 遗憾的是, MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。

1.3 多表连结

通常连结只涉及 2 张表,但有时也会出现必须同时连结 3 张以上的表的情况, 原则上连结表的数量并没有限制。

1.3.1 多表进行内连结

首先创建一个用于三表连结的表 InventoryProduct.首先我们创建一张用来管理库存商品的表, 假设商品都保存在 P001 和 P002 这 2 个仓库之中。 ​ 建表语句如下: CREATE TABLE InventoryProduct( inventory_id CHAR(4) NOT NULL,product_id CHAR(4) NOT NULL,inventory_quantity INTEGER NOT NULL,PRIMARY KEY (inventory_id, product_id));然后插入一些数据: --- DML：插入数据START TRANSACTION;INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P001', '0001', 0);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P001', '0002', 120);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P001', '0003', 200);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P001', '0004', 3);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P001', '0005', 0);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P001', '0006', 99);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P001', '0007', 999);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P001', '0008', 200);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P002', '0001', 10);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P002', '0002', 25);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P002', '0003', 34);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P002', '0004', 19);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P002', '0005', 99);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P002', '0006', 0);INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P002', '0007', 0 );INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)VALUES ('P002', '0008', 18);COMMIT;接下来, 我们根据上表及 ShopProduct 表和 Product 表, 使用内连接找出每个商店都有那些商品, 每种商品的库存总量分别是多少。 SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.sale_price ,IP.inventory_quantity FROM ShopProduct AS SP INNER JOIN Product AS P ON SP.product_id = P.product_id INNER JOIN InventoryProduct AS IP ON SP.product_id = IP.product_id WHERE IP.inventory_id = 'P001';得到如下结果 ​ 我们可以看到, 连结第三张表的时候, 也是通过 ON 子句指定连结条件(这里使用最基础的等号将作为连结条件的 Product 表和 ShopProduct 表中的商品编号 product _id 连结了起来), 由于 Product 表和 ShopProduct 表已经进行了连结,因此就无需再对 Product 表和 InventoryProduct 表进行连结了(虽然也可以进行连结,但结果并不会发生改变, 因为本质上并没有增加新的限制条件)。 即使想要把连结的表增加到 4 张、5 张……使用 INNER JOIN 进行添加的方式也是完全相同的。

1.3.2 多表进行外连结

例如, SELECT P.product_id ,P.product_name ,P.sale_price ,SP.shop_id ,SP.shop_name ,IP.inventory_quantity FROM Product AS P LEFT OUTER JOIN ShopProduct AS SPON SP.product_id = P.product_idLEFT OUTER JOIN InventoryProduct AS IPON SP.product_id = IP.product_id查询结果 ​

1.4 ON 子句进阶--非等值连结

在刚开始介绍连结的时候, 书上提到过, 除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。

1.4.1 非等值自左连结(SELF JOIN)

使用非等值自左连结实现排名。 练习题: 希望对 Product 表中的商品按照售价赋予排名. 一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 例如, 对于价格最高的商品, SELECT product_id ,product_name ,sale_price ,COUNT(p2_id) AS my_rank FROM (--使用自左连结对每种商品找出价格不低于它的商品 SELECT P1.product_id ,P1.product_name ,P1.sale_price ,P2.product_id AS P2_id ,P2.product_name AS P2_name ,P2.sale_price AS P2_price FROM Product AS P1 LEFT OUTER JOIN Product AS P2 ON P1.sale_price <= P2.sale_price ) AS X GROUP BY product_id, product_name, sale_price ORDER BY my_rank;注 1: COUNT 函数的参数是列名时, 会忽略该列中的缺失值, 参数为 * 时则不忽略缺失值。 注 2: 上述排名方案存在一些问题--如果两个商品的价格相等, 则会导致两个商品的排名错误, 例如, 叉子和打孔器的排名应该都是第六, 但上述查询导致二者排名都是第七. 试修改上述查询使得二者的排名均为第六。 ​ 注 3: 实际上, 进行排名有专门的函数, 这是 MySQL 8.0 新增加的窗口函数中的一种(窗口函数将在下一章学习), 但在较低版本的 MySQL 中只能使用上述自左连结的思路。 使用非等值自左连结进行累计求和: 练习题: 请按照商品的售价从低到高,对售价进行累计求和[注:这个案例缺少实际意义, 并且由于有两种商品价格相同导致了不必要的复杂度, 但示例数据库的表结构比较简单, 暂时未想出有实际意义的例题] 首先, 按照题意, 对每种商品使用自左连结, 找出比该商品售价价格更低或相等的商品 SELECT P1.product_id ,P1.product_name ,P1.sale_price ,P2.product_id AS P2_id ,P2.product_name AS P2_name ,P2.sale_price AS P2_price FROM Product AS P1 LEFT OUTER JOIN Product AS P2 ON P1.sale_price >= P2.sale_price ORDER BY P1.sale_price,P1.product_id查看查询结果 ​ 看起来似乎没什么问题。 下一步, 按照 P1.product_Id 分组,对 P2_price 求和: SELECT product_id ,product_name ,sale_price ,SUM(P2_price) AS cum_price FROM (SELECT P1.product_id ,P1.product_name ,P1.sale_price ,P2.product_id AS P2_id ,P2.product_name AS P2_name ,P2.sale_price AS P2_price FROM Product AS P1 LEFT OUTER JOIN Product AS P2 ON P1.sale_price >= P2.sale_price ORDER BY P1.sale_price,P1.product_id ) AS X GROUP BY product_id, product_name, sale_price ORDER BY sale_price,product_id;得到的查询结果为: ​ 观察上述查询结果发现, 由于有两种商品的售价相同, 在使用 >= 进行连结时, 导致了累计求和错误, 这是由于这两种商品售价相同导致的. 因此实际上之前是不应该单独只用 >= 作为连结条件的. 考察我们建立自左连结的本意, 是要找出满足:1.比该商品售价更低的, 或者是 2.该种商品自身,以及 3.如果 A 和 B 两种商品售价相等,则建立连结时, 如果 P1.A 和 P2.A,P2.B 建立了连接, 则 P1.B 不再和 P2.A 建立连结, 因此根据上述约束条件, 利用 ID 的有序性, 进一步将上述查询改写为: SELECTproduct_id, product_name, sale_price ,SUM(P2_price) AS cum_price FROM (SELECT P1.product_id, P1.product_name, P1.sale_price ,P2.product_id AS P2_id ,P2.product_name AS P2_name ,P2.sale_price AS P2_price FROM Product AS P1 LEFT OUTER JOIN Product AS P2 ON ((P1.sale_price > P2.sale_price) OR (P1.sale_price = P2.sale_price AND P1.product_id<=P2.product_id)) ORDER BY P1.sale_price,P1.product_id) AS X GROUP BY product_id, product_name, sale_price ORDER BY sale_price,cum_price;这次结果就正确了. ​

1.5 交叉连结—— CROSS JOIN(笛卡尔积)

之前的无论是外连结内连结, 一个共同的必备条件就是连结条件--ON 子句, 用来指定连结的条件. 如果你试过不使用这个连结条件的连结查询, 你可能已经发现, 结果会有很多行. 在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积, 后者是一个数学术语. 两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合. 数据库表(或者子查询)的并,交和差都是在纵向上对表进行扩张或筛选限制等运算的, 这要求表的列数及对应位置的列的数据类型"相容", 因此这些运算并不会增加新的列, 而交叉连接(笛卡尔积)则是在横向上对表进行扩张, 即增加新的列, 这一点和连结的功能是一致的. 但因为没有了ON子句的限制, 会对左表和右表的每一行进行组合, 这经常会导致很多无意义的行出现在检索结果中. 当然, 在某些查询需求中, 交叉连结也有一些用处。 交叉连结的语法有如下几种形式: -- 1.使用关键字 CROSS JOIN 显式地进行交叉连结SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.sale_price FROM ShopProduct AS SP CROSS JOIN Product AS P;--2.使用逗号分隔两个表,并省略 ON 子句SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.sale_price FROM ShopProduct AS SP , Product AS P;请大家试着执行一下以上语句. 可能大家会惊讶于结果的行数, 但我们还是先来介绍一下语法结构吧.对满足相同规则的表进行交叉连结的集合运算符是 CROSS JOIN （笛卡儿积）.进行交叉连结时无法使用内连结和外连结中所使用的ON 子句,这是因为交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积.本例中,因为 ShopProduct 表存在 13 条记录,Product 表存在 8 条记录,所以结果中就包含了 13 × 8 = 104 条记录。 可能这时会有读者想起前面我们提到过集合运算中的乘法会在本节中进行详细学习,这就是上面介绍的交叉连结.内连结是交叉连结的一部分,“内”也可以理解为“包含在交叉连结结果中的部分”.相反,外连结的“外”可以理解为“交叉连结结果之外的部分”。 交叉连结没有应用到实际业务之中的原因有两个.一是其结果没有实用价值,二是由于其结果行数太多,需要花费大量的运算时间和高性能设备的支持。

1.5.1 [扩展阅读]连结与笛卡儿积的关系

考察笛卡儿积和连结, 不难发现, 笛卡儿积可以视作一种特殊的连结(事实上笛卡儿积的语法也可以写作 CROSS JOIN), 这种连结的 ON 子句是一个恒为真的谓词。 反过来思考, 在对笛卡儿积进行适当的限制之后, 也就得到了内连结和外连结。 例如, 对于 ShopProduct 表和 Product 表, 首先建立笛卡尔乘积: SELECT SP.*, P.* FROM ShopProduct AS SP CROSS JOIN Product AS P;查询结果的一部分如下: ​ 然后对上述笛卡尔乘积增加筛选条件 SP.product_id=P.product_id, 就得到了和内连结一致的结果: SELECT SP.*, P.* FROM ShopProduct AS SP CROSS JOIN Product AS P WHERE SP.product_id = P.product_id;查询结果如下: 实际上, 正如书中所说, 上述写法中, 将 CROSS JOIN 改为逗号后, 正是内连结的旧式写法, 但在 ANSI 和 ISO 的 SQL-92 标准中, 已经将使用 INNER JOIN ..ON.. 的写法规定为标准写法, 因此极力推荐大家在平时写 SQL 查询时, 使用规范写法。

1.6 连结的特定语法和过时语法

在笛卡尔积的基础上, 我们增加一个 WHERE 子句, 将之前的连结条件作为筛选条件加进去, 我们会发现, 得到的结果恰好是直接使用内连接的结果。 试执行以下查询, 并将查询结果与内连结一节第一个例子的结果做对比。 SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.sale_price FROM shopproduct AS SP CROSS JOIN product AS P WHERE SP.product_id = P.product_id;我们发现, 这两个语句得到的结果是相同的。 之前我们学习的内连结和外连结的语法都符合标准 SQL 的规定,可以在所有 DBMS 中执行,因此大家可以放心使用. 但是如果大家之后从事系统开发工作, 或者阅读遗留SQL 查询语句的话,一定会碰到需要阅读他人写的代码并进行维护的情况,而那些使用特定和过时语法的程序就会成为我们的麻烦。 SQL 是一门特定语法及过时语法非常多的语言,虽然之前本书中也多次提及,但连结是其中特定语法的部分,现在还有不少年长的程序员和系统工程师仍在使用这些特定的语法.例如,将本节最初介绍的内连结的 SELECT 语句替换为过时语法的结果如下所示。 使用过时语法的内连结（结果与代码清单 7-9 相同） SELECT SP.shop_id ,SP.shop_name ,SP.product_id ,P.product_name ,P.sale_price FROM shopproduct SP, product P WHERE SP.product_id = P.product_id AND SP.shop_id = '000A';这样的书写方式所得到的结果与标准语法完全相同,并且这样的语法可以在所有的 DBMS 中执行,并不能算是特定的语法,只是过时了而已。 但是,由于这样的语法不仅过时,而且还存在很多其他的问题,因此不推荐大家使用,理由主要有以下三点: 第一,使用这样的语法无法马上判断出到底是内连结还是外连结（又或者是其他种类的连结）。 第二,由于连结条件都写在 WHERE 子句之中,因此无法在短时间内分辨出哪部分是连结条件,哪部分是用来选取记录的限制条件。

用户评论

爱你的小笨蛋

终于找到一个讲解JOIN的超详细教程了！之前总是搞不明白它

    有17位网友表示赞同！

三年约

想要提升SQL水平，JOIN是必不可少的啊，这篇文章太棒了!

    有19位网友表示赞同！

淡写薰衣草的香

学习数据库几年了，这个JOIN用法还是第一次看到这么细致的解释，受益匪浅。

    有13位网友表示赞同！

面瘫脸

表连接到底怎么才能用好？这家教程讲明白了！

    有15位网友表示赞同！

相知相惜

感谢作者分享！以后不用再纠结JOIN的复杂用法啦！ super useful

    有20位网友表示赞同！

命该如此

学习数据库新手在此，感觉这个SQL JOIN用法详解通俗易懂

    有10位网友表示赞同！

◆乱世梦红颜

INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN，都给举了例子，太棒了！

    有13位网友表示赞同！

愁杀

SQL进阶确实离不开JOIN的熟练运用，收藏了这篇文章！

    有9位网友表示赞同！

有一种中毒叫上瘾成咆哮i

终于解开了JOIN的迷团!

    有9位网友表示赞同！

雨后彩虹

超详细超实用？ 这标题有点厉害啊！期待学习

    有11位网友表示赞同！

景忧丶枫涩帘淞幕雨

看了之后感觉JOIN用法并不复杂，只是实践少一点罢了!

    有14位网友表示赞同！

肆忌

表连接简直是SQL基础中的基础，需要好好精研一下 JOIN 用法！

    有18位网友表示赞同！

一纸愁肠。

对不同类型JOIN的理解比之前深了好多，谢谢分享!

    有11位网友表示赞同！

拥抱

学习资料不多，终于找到了这样详细的SQL教程！

    有5位网友表示赞同！

无关风月

这篇教程讲得真好，真想找到作者请吃饭！

    有10位网友表示赞同！

墨染天下

做数据分析工作的朋友应该都熟悉JOIN吧？

    有14位网友表示赞同！

回忆未来

这篇文章让我对SQL JOIN用法有了更清晰的认识！

    有19位网友表示赞同！

桃洛憬

之前看资料总是觉得JOIN太难懂了, 这篇教程简直是解惑神器!

    有9位网友表示赞同！

铁树不曾开花

收藏，以后学习SQL的时候可以拿来参考！

    有12位网友表示赞同！