干货分享 | SQL join 你真的懂了吗？

 

如果将数据存储在关系数据库中，最好将数据规范化，通常需要将数据拆分为通过键逻辑连接的多个表。大多数非简易的查询都需要多个表的连接来完成。本文简要介绍了SQL连接，重点介绍了内连接和外接之间的区别。

 

一、 交叉连接  

 

要理解SQL连接，最好从交叉连接开始，交叉连接的简单语句如下：

select * from t1, t2;

在这篇文章中，我们将使用两个名为t1和 t2的示例表：

    t1                         t2
 id |   name              code | id
----+----------          ------+----
  1 | Joanne              x    |  2
  2 | Sam                 z    |  3
  3 | Emmanuel            a    |  7
  4 | Brayden

     t1                         t2
 id |   name              code | id
----+----------          ------+----
  1 | Joanne              x    |  2
  2 | Sam                 z    |  3
  3 | Emmanuel            a    |  7
  4 | Brayden

运行交叉连接结果如下：

 id |   name   | code | id
----+----------+------+----
  1 | Joanne   | x    |  2
  2 |  Sam      | x    |  2
  3 | Emmanuel | x    |  2
  4 |  Brayden  | x    |  2

交叉连接在两个表之间执行叉乘(或笛卡尔积)。对于t1中的每一行，它从t2中添加所有可能的行。得到的表包含t1和t2的所有列，它的行数是t1和t2行数的乘积。

交叉连接是一个很好的起点，因为它们使内部连接更容易理解。它们也是关系代数连接的基础。SQL还支持更显式方法调用交叉连接：

select * from t1 cross join t2;

此语句和前面等价

 

二、内连接  

 

 SQL查询的一个重要组成是使用where子句过滤结果。例如，我们可以在前面显示的交叉连接上创建以下过滤：

select * from t1, t2 where t2.code = 'x' and t1.name like '%d%'

结果：

 id |   name   | code | id
----+----------+------+----
  4 | Brayden  | x    |  2

在跨越两个表时特别有用的一个过滤器是检查某个列值是否匹配。t1和t2都有一个id列;我们假设这些id指向相同的东西，并且希望找到id匹配的两个表中的所有行组合。我们可以：

select * from t1, t2 where t1.id = t2.id;

结果：

id |   name   | code | id
----+----------+------+----
  2 | Sam      | x    |  2
  3 | Emmanuel | z    |  3

这种过滤非常有用，它有自己的概念：内部连接[1]：

select * from t1 inner join t2 on t1.id = t2.id;

它生成完全相同的结果表。当我们比较的两个表中列的名称相同时，可以使用更短的语法：

select * from t1 inner join t2 using (id);

这个结果将只有一个id列，因为我们明确表示用id匹配：

 id |   name   | code
----+----------+------
  2 | Sam      | x
  3 | Emmanuel | z

过滤对于理解内部连接非常有用。只要记住它是两个表的叉乘，其中只有满足特定条件的行才会返回。您可能想知道使用where filter和inner join之间的区别，虽然这两者在逻辑上是等价的，但有一些注意点：

• 至少在理论上，inner join ... on更有效，因为在常见的多表连接中，我们可以对每个连接应用过滤，而不是在一个大表的末尾应用过滤。然而，对于现代SQL查询优化器，还不清楚这是否是一个真正的优势，优化器很可能会为这两者生成完全相同的底层操作序列。
• 就可读性而言，能够在连接本身的附近查看连接是什么，而不是在查询的末尾使用一个大型where过滤器，这样会更好，这对于多表连接非常重要。
  例如，考虑客户下订单，订单细节在一个单独的表中(因为客户可以有多个订单)。我们可以有一个复杂的连接：

select *
  from customers, orders, order_details
  where customers.id = order_details.customerid and
        orders.id = order_details.orderid

相比：

select *
  from customers
    inner join order_details on customer.id = order_details.customerid
inner join orders on orders.id = order_details.orderid

后者每个连接的标准更加清晰。

 

最后，一些数据库还支持自连接，这是“具有相同名称的列上的内部连接表”的快捷方式。下面的查询等价于上面使用的变量：

select * from t1 natural join t2;

自连接是来自关系代数的术语，它在SQL查询中并不常用。

 

三、 外连接  

 

内连接作为叉乘的特殊情况很容易理解，外连接要复杂一些。幸运的是，一旦理解了内连接，就不难探索外连接，因此我们可以一步一步地构建这些知识。

 

回到表t1和t2。我们可以使用(id)为内部连接分配逻辑意义，例如“显示所有匹配(from t1) names的(from t2)代码”。结果是在两个表中找到id匹配的两行。

 

然而有时我们想要一些稍微不同的东西，我们想要问“如果有匹配的names (from t1)和代码(from t2)，请显示它们的所有names (from t1)和代码(from t2)”。换句话说，我们希望所有的names都在结果中，可能在t2表中没有找到匹配的代码，可使用null值[2]。

 

让我们把这个请求拆分。我们希望：

•所有t1中的names与t2中的代码匹配

•来自t1的所有names在t2中没有匹配，返回为null

在SQL中我们可以这样表达：

select id, name, code
    from t1 inner join t2 using (id)
  union
select id, name, null
    from t1 where id not in (select id from t2);

有几点需要注意：

•第一个查询正是上一节中的内部连接，它回答了第一个部分。

•第二个查询使用子查询列出t2中不匹配的所有名称。

•我们在这里显式地列出列名，因为列名必须与两个统一的表完全匹配。

我们刚刚编写的在SQL[3]中称为左外连接，可以更容易地编写为：

select * from t1 left outerjoin t2 using (id);

结果：

 id |   name   | code
----+----------+------
  2 | Sam      | x
  3 | Emmanuel | z
  4 | Brayden  |
  1 | Joanne   |

这是左外连接，因为我们希望左边表中的所有行都出现在结果中。正如你可能已经猜到的，还有一个右外连接：

select * from t1 right outerjoin t2 using (id);
 id |   name   | code
----+----------+------
  2 | Sam      | x
  3 | Emmanuel | z
  7 |          | a

这里，右边表中的所有行都出现在结果中，如果找到，左边的列(name)将匹配，否则为null。

 

最后，我们可能希望连接两边的行总是出现在结果表中。这叫做全外联合：

select * from t1 full outerjoin t2 using (id);

结果：

 id |   name   | code
----+----------+------
  2 | Sam      | x
  3 | Emmanuel | z
  7 |          | a
  4 | Brayden  |
  1 | Joanne   |

完整的外部连接可以直接使用左和右连接的联合来表示：

select * from t1 left join t2 using (id)
  union
select * from t1 right join t2 using (id);

不过有一个小小的警告，当union删除重复项时，完整的外部连接不删除;因此，在某些特殊情况下，结果可能会有所不同。如果您关心在输出中看到重复，而数据库不支持完整的外部连接，这是一种更准确的写法(尽管效率较低)：

select * from t1 left join t2 using (id)
union all
select * from t1 right join t2 using (id) where t1.id is null;

四、 多列连接  

 

到目前为止的示例显示了单个列id上的连接，虽然这是最常见的情况，但有时会使用更复杂的匹配标准。SQL不将join的语法限制为单个条件，所以我们可以在多个列和任意条件上进行连接。让我们在我们的两个表中添加另一列：

  t1                             t2
 id |   name   | ranking        code | id | ranking
----+----------+---------      ------+----+--------
  1 | Joanne   |       7        x    |  2 |       8
  2 | Sam      |       7        z    |  3 |       6
  3 | Emmanuel |       6
  4 | Brayden  |       2

我们可以根据 id 和ranking连接：

select * from t1 inner join t2 on t1.id = t2.id and t1.ranking = t2.ranking;

结果：

 id |   name   | ranking | code | id | ranking
----+----------+---------+------+----+---------
  3 | Emmanuel |       6 | z    |  3 |       6

加上 using：

select * from t1 inner join t2 using (id, ranking);

结果：

 id | ranking |   name   | code
----+---------+----------+------
  3 |       6 | Emmanuel | z

同样，我们可以运行外部连接：

select * from t1 left outerjoin t2 using (id, ranking);

结果：

 id | ranking |   name   | code
----+---------+----------+------
  3 |       6 | Emmanuel | z
  2 |        7 | Sam      |
  4 |       2 | Brayden  |
  1 |        7 | Joanne   |

 

五、  多个表上的连接 

 

在实际的数据库中，数据常常被分割成多个表，查询探测4-5个或更多的表来收集所有的信息并不少见。让我们以三表为例。我们会有一张顾客表和一张商品表：

  customers                       items
 customerid |   name          itemid | description | price
------------+----------      --------+-------------+-------
          1 | Robert               1 | Napkins     |   1.5
          2 | Jennifer             2 | Granola     |  4.25
          3 | Yoshi                3 | Cheese      |     3
          4 | Xi

此外，我们将有一个linking 表，以记录客户的订单：

 customerid | itemid | orderdate
------------+--------+------------
          1 |      2 | 2019-03-02
          1 |      3 | 2019-03-02
          1 |      1 | 2019-03-03
          2 |      1 | 2019-02-22
          3 |      3 | 2019-01-15
          3 |      2 | 2019-02-20
          4 |      3 | 2019-02-21
          4 |      3 | 2019-02-22

我们可能对所有订购奶酪的顾客以及订购日期感兴趣。这需要连接所有三个表:

select name, orderdate, description
    from (customers
    inner join orders using (customerid))
    inner join items using (itemid)
    where items.description = 'Cheese';

结果：

  name  | orderdate  | description
--------+------------+-------------
 Robert | 2019-03-02 | Cheese
 Yoshi  | 2019-01-15 | Cheese
 Xi     | 2019-02-21 | Cheese
 Xi     | 2019-02-22 | Cheese

注意第一个连接的参数项，对于这个查询，这并不是必需的，但是我发现显式地控制连接的顺序很有用。我们可以加入任意多的表，但是顺序必须合理。每个连接生成一个新的逻辑表，这个逻辑表参与其他连接。

参考文献：
[1] When we usethe join keyword in SQL, inner joinis the default, so the keyword inner is optional.That said, to distinguish inner joins from outer joins IMHO it's preferable tobe explicit.

[2] This soundscontrived with our simplistic tables, but in reality it's an extremely commondatabase query. Imagine our t1 iscustomers with unique IDs and names, and our t2 issome code assigned to each customer. Suppose we want to display all ourcustomers, regardless of who already has a code assigned. For customers that dohave a code we want to show it.

[3] With thekeyword left before a join, the keyword outer isoptional, so we could just say left joininstead of left outer join. Ilike the explicitness of having outer there. The same appliesfor right and full joins.

 

文章转自公众号：腾讯云数据库