SQL 魔法第一部分 - JOIN
4.81/5 (41投票s)
本系列文章的第一篇，
引言
欢迎来到这篇我多年来的第一篇文章。我打算每周写一篇关于 SQL 的系列文章。我知道我写的可能都是网站上已有的内容，但我的目标是提供一个组织有序的文章系列，让人们可以像看书一样逐步学习。我猜想很多开发者都使用数据库，懂一点 SQL，但并不真正理解如何充分利用它。希望我能有所帮助。背景
这第一篇文章将从基础开始。我假设您知道如何编写 'where' 子句，并且我将从讨论连接表以在它们之间选择数据的方法开始。如果您还没有下载本文的代码，请立即下载。它包含了您在阅读本文时将在 Management Studio 中运行的所有 SQL。现在就下载吧。我等您……好了，回来了吗？您看到的第一个代码会像这样：with listall(id) as
(
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
),
listodd(id) as
(
select 1 union all
select 3 union all
select 5 union all
select 7 union all
select 9 union all
select 11
),
listeven(id) as
(
select 2 union all
select 4 union all
select 6 union all
select 8 union all
select 10
)如果您不知道这是什么（这是通用表表达式 CTE，是第二篇文章的主题），请不用担心。简单地说，这会在内存中创建三个表，一个包含序列 1-10，一个只包含奇数到 11，另一个只包含偶数到 10。INNER JOIN (内连接)
我假设每个人都知道这是做什么的。select * from listall它返回 listall 表的内容（1-10）。那么，这是做什么的呢？select * from listall la inner join listodd lo on lo.id = la.id它返回奇数序列到 9，因为内连接只会返回两个表能够连接的行集。这在您有一个订单表，其中包含客户 ID，还有一个客户表，该 ID 提供了客户的所有详细信息时非常有用。那么，这是做什么的呢？select * from listeven le inner join listodd lo on lo.id = le.id它不返回任何内容，因为奇数行和偶数行没有共同的行。OUTER JOIN (外连接)
那么，这是做什么的呢？select * from listall lo left outer join listeven le on lo.id = le.id它返回 SELECT 语句左侧表的所有行，即使连接不匹配。在连接不匹配的地方，会返回 null。当您使用连接来检查某项是否存在时，这很有用，您可以使用 case 语句根据是否找到匹配项来设置值，如下所示：select lo.id, isEven = case when le.id is null then 0 else 1 end from listall lo left outer join listeven le on lo.id = le.id您还可以使用 COALESCE 或 ISNULL 来设置默认值，如下所示：select lo.id, isnull(le.id, -1) from listall la left outer join listeven le on la.id = le.id如您所见，在 SQL 中，您可以做的不仅仅是返回原始数据。那么，这是做什么的呢？select * from listeven le left outer join listall la on la.id = le.id它只返回偶数。连接在左侧，而最左边的表只包含偶数。要获得相同的结果，如果您需要这个表顺序，您可以这样做：select * from listeven le right outer join listall la on la.id = le.id如果您需要两侧都有 null，您可以这样做：select * from listeven le full outer join listodd lo on lo.id = le.id请注意，如果您尝试使用 orderby 进行排序，您会发现 null 值会被排在最前面。要再次排序，您应该这样做：select coalesce(le.id, lo.id) as idList, le.id, lo.id from listeven le full outer join listodd lo on lo.id = le.id order by idlist正如您可能知道的，coalesce 和 isnull 一样，如果第一个值为 null，则返回第二个值。区别在于 coalesce 只做这个，而 ISNULL 可以将表达式作为第一个参数。有一点要补充，'OUTER' 这个词是可选的，您可以只指定 'left join'、'right join' 或 'full join'。CROSS JOIN (交叉连接)
这可能是人们最不了解的一种。您期望它做什么？select * from listeven le cross join listodd lo如果您运行它，您将得到 30 个结果。listeven 中有 5 个数字，listodd 中有 6 个。5 乘以 6 等于 30 个结果。交叉连接会将表 1 中的每一行与表 2 中的每一行组合起来。这是一个非常强大的技术，如果使用得当。稍后可能会有一篇文章介绍使用交叉连接可以做的很酷的事情，我确定下周的文章也会涉及交叉连接。正如您所见，因为它返回的是所有可能的组合，所以没有要连接的列。事实上，如果两个表有同名字段，两个值都会被返回，您将有两个同名字段，除非您像这样给它们起别名：select le.id, lo.id as id2 from listeven le cross join listodd lo您不必返回所有交叉连接的数据，您可以像任何其他 SELECT 一样指定列并为它们起别名。交叉连接的简写形式是逗号，例如：select * from listeven, listodd
然而，虽然您可能会在在线示例中看到这种用法，但它在 20 多年以来已经不是“有效”的 SQL 了，并且没有数据库实现被要求支持它，所以您不应该在自己的代码中使用它。
INTERSECT (交集)
虽然它们不算是真正的连接，但我还是要介绍另外两件事。第一件是 'intersect'。它的工作原理如下：select * from listall
intersect
select * from listodd在这种情况下，listodd 中有一个值不在 listall 中（11），所以结果是 listodd 的内容，减去 11。有时您会有两个结果集，并且您想知道它们之间有哪些共同的值。INTERSECT 允许您做到这一点。当然，这可以通过交叉连接两个包含任意数量连接和其他操作的 SELECT 语句来实现。Select * 是可接受的语法，但要求两个表具有相同的列名和数据类型。请注意，有时您只关心一个列的交集（通常是主键），在这种情况下，最好创建只返回主键值的 CTE，以及一个返回所有数据的 CTE，这样您最终可能会得到类似这样的代码（这个不在文件中，因为它无法运行）：select * from myClientList where clientid in ( select clientid from list1 intersect select clientid from list2)当然，这会放在定义三个可能很复杂的 CTE 的语句的底部。希望您能看到 CTE 如何提高 SQL 代码的可读性和可维护性。EXCEPT (差集)
我最近有一个任务，需要将一个大型数据集从一个数据库/格式转换到另一个。我们有大量的复杂业务规则，所以我最终得到了一个视图，它从旧数据库中提取所有数据，许多视图用于处理不同的数据类型，然后顶部的视图汇总所有这些值，并将它们再次转换成一个列表。这非常复杂，但最终，我们想确保没有遗漏任何记录。这正是 'EXCEPT' 操作无价的场合。select * from listall
except
select * from listodd这会返回第一个表中所有不在第二个表中的内容（但反之则不成立）。因此，虽然数字 '11' 在 listodd 中而不在 listall 中，但它不会被此操作返回。在我的例子中，这返回了我基本列表中的所有文档，这些文档不在顶级列表中，因此没有满足任何业务规则，否则就会丢失。鉴于我们的数据转换的性质，我的查询会更复杂一些（它是三个 SELECT 的联合），我不得不只对主键进行 EXCEPT 操作，然后基于该操作选择我所有的数据。如果任何一行不同，数据将被视为不同，所以如果您只想找出哪些记录不存在（无论它们是否以任何方式被转换），那么只对键进行操作就是您需要的。UNION 和 UNION ALL (联合和联合全部)
我差点忘了讲这些。如果我们给定的数据集，我们想要一个从 1 到 11 的序列呢？这将做到这一点：select * from listall
union
select * from listodd
order by id它返回两个表中值的组合，删除重复项。如果您想查看所有值，包括重复项，请这样做：select * from listall
union all
select * from listodd
order by id
我见过一些非常糟糕的基于 union 的 SQL 代码。请记住，union 的意思是运行这些独立的 SQL 查询，然后合并它们。如果您能创建一个查询，它一定会更快（但 UNION 本身非常快，我的意思是，如果您运行两个复杂的查询，您需要支付两个查询的成本，而不是只运行一个）。然而，UNION 在我前面描述的任务中是无价的，因为我有一个基本视图，以及针对不同业务规则的不同视图，然后需要将它们重新组合起来。它确实很慢，但它是当时手头最好的工具。
几个人建议我添加一个不同连接类型的可视化表示。CP 上已经有一篇很棒的文章做到了这一点，您可以在这里看到。我没有理由重复它，因为寻求更多信息的人阅读那篇文章以及我的文章会更好。我想说的是，作者展示的“排除连接”对于我展示的技术来说是完全有效的，我会使用 'EXCEPT' 关键字来实现相同的结果。我不确定哪个更快。
正如您所见，SQL 的内容远不止 SELECT 语句和内连接。下周我将解释如何充分利用 CTE。如果您有任何希望我特别涵盖的内容，甚至是一般性的 SQL 问题，请尽管提问。我保证会回答每一个问题，即使有时我不得不承认我不确定（但我会努力找出答案）。
第二部分（介绍 SELECT 语句）现在可以在这里阅读。