SQL 查询是从 Select 开始的吗？

杰哥的IT之旅

发布于 2021-08-06 14:49:56

1.7K0

发布于 2021-08-06 14:49:56

文章被收录于专栏：GitHub专栏GitHub专栏

好吧，显然很多SQL查询都是从SELECT开始的（实际上本文只是关注SELECT查询，而不是INSERT或其它别的什么）。

但是！昨天我正在做窗口函数的解释说明，并且我发现自己在谷歌上搜索“你能根据窗口函数的结果进行过滤吗”。比如 — 你能在WHERE、HAVING或者其它地方过滤窗口函数的结果吗？

最后我得出的结论是：“窗口函数必须在WHERE和GROUP BY之后运行，所以你做不到”。但这让我想到了一个更大的问题 — SQL查询的实际运行顺序是什么？

这是我凭直觉就知道的事情（“我肯定知道！我已经编写了至少10000个SQL查询，其中一些非常复杂！），但我很难真正地准确说出顺序是什么。

1、SQL查询按此顺序进行

这就是我查找到的顺序！（SELECT并不是在第一步执行，而是到第五步才执行）

（这里是一篇推特：https://twitter.com/b0rk/status/1179449535938076673）

（我真的很想找到一种比“sql查询按此顺序发生/运行”更准确的表达方式，但我还没想出来。）

在非图形格式中，其顺序为：

l FROM/JOIN 和所有的 ON 条件
l WHERE
l GROUP BY
l HAVING
l SELECT（包括窗口函数）
l ORDER BY
l LIMIT

2、图解此图有助于你做出回答

此图是关于SQL查询的语义的 — 你可以通过它，对给定查询将返回什么结果进行推理，并回答如下问题：

我能在一个GROUP BY的结果上执行WHERE么？（不行！WHERE发生在GROUP BY之前！）
我可以根据窗口函数的结果进行过滤吗（不行！窗口函数发生在SELECT中，它发生在WHERE和GROUP BY之后）
我可以基于GROUP BY中所做的来进行ORDER BY么？（可以！ORDER BY是最后执行的基本步骤，你可以根据任何东西做ORDER BY！）
LIMIT何时执行？（在最后！）

3、数据库引擎实际并不是按这个顺序运行查询

因为它实现了一系列优化以使查询运行得更快 — 我们稍后将在本文中讨论这一点。

所以：

当你只想了解哪些查询是有效的，以及如何推理给定查询的结果时，可以使用此图。
你不应该使用此图来解释查询性能或任何有关索引的事情，那是一个复杂得多的问题，涉及更多变量。

4、混淆因素：列别名

有人在Twitter上指出，许多SQL实现允许你使用以下语法：

SELECT CONCAT(first_name, ' ', last_name) AS full_name, count(*)
FROM table
GROUP BY full_name

此查询使其看起来像是在SELECT之后才发生GROUP BY，即使GROUP BY先执行，因为GROUP BY引用了SELECT中的别名。但是要使GROUP BY发挥作用，其实并不需要在SELECT之后才运行 — 数据库引擎只要将查询重写为：

SELECT CONCAT(first_name, ' ', last_name) AS full_name, count(*)
FROM table
GROUP BY CONCAT(first_name, ' ', last_name)

并且首先运行GROUP BY。

你的数据库引擎肯定还会在开始运行查询之前执行一系列检查，确保你在SELECT和GROUP BY中放置的内容合在一起是有意义的，因此在开始制定执行计划之前，它必须将查询作为一个整体来查看。

5、查询实际上不是按此顺序运行的（优化！）

实际上，数据库引擎并不是真的通过连接、然后过滤、然后再分组来运行查询，因为它们实现了一系列优化，只要重新排列执行顺序不改变查询结果，就可以重排以使查询运行得更快。

一个简单的例子说明了为什么需要以不同的顺序运行查询以使其快速运行，在这个查询中：

SELECT * FROM
owners LEFT JOIN cats ON owners.id = cats.owner
WHERE cats.name = 'mr darcy'

如果你只需要查找3个名为“mr darcy”的猫，那么执行整个左连接并匹配这两个表中的所有行是非常愚蠢的 —— 首先对名为“mr darcy”的猫进行一些筛选要快得多。在这种情况下，先进行过滤不会改变查询结果！

实际上，数据库引擎还实现了许多其它优化，这些优化可能会使它们以不同的顺序运行查询，但不能再说了，老实讲，这方面我不是专家。

6、LINQ以FROM开始查询

LINQ（一种C#和VB.NET中的查询语法）使用的顺序为FROM … WHERE … SELECT。下面是一个LINQ查询的示例：

var teenAgerStudent = from s in studentList
                      where s.Age > 12 && s.Age < 20
                      select s;

pandas（我所喜欢的数据治理工具：https://github.com/jvns/pandas-cookbook）也基本上是这样工作的，尽管你不需要使用这种精确的顺序 — 我经常会这样编写pandas代码：

df = thing1.join(thing2)      # like a JOIN
df = df[df.created_at > 1000] # like a WHERE
df = df.groupby('something', num_yes = ('yes', 'sum')) # like a GROUP BY
df = df[df.num_yes > 2]       # like a HAVING, filtering on the result of a GROUP BY
df = df[['num_yes', 'something1', 'something']] # pick the columns I want to display, like a SELECT
df.sort_values('sometthing', ascending=True)[:30] # ORDER BY and LIMIT
df[:30]

这并不是因为pandas对如何编写代码强加了任何特定规则。只是按照JOIN / WHERE / GROUP BY / HAVING的顺序编写代码通常好理解。（不过，我经常会先放一个WHERE来提高性能，而且我认为大多数数据库引擎实际也会先执行WHERE）

在R的dplyr中，你还能使用不同的语法来查询诸如Postgres、MySQL或SQLite等SQL数据库，它们的顺序也更符合逻辑。

不知道这一点令我自己着实惊讶

我写了这样一篇博文，因为当我发现这个顺序的时候非常惊讶，我以前从来没有看到过它被这样写下来 — 它基本上解释了我凭直觉所知道的，关于为什么一些查询被允许而另一些不被允许的一切。所以我想把它写下来，希望它能帮助其他人理解如何编写SQL查询。