在阅读它之后，这不是显式与隐式SQL连接的副本。 答案可能相关(甚至相同)，但问题是不同的。
它们之间有什么不同?每一种都应该有什么不同?
如果我正确地理解了这个理论，那么查询优化器应该能够互换地使用这两种方法。
当前回答
我认为这是连接序列效应。 在左上角连接的情况下，SQL先做左连接，然后做where过滤器。 在沮丧的情况下，找到订单。首先ID=12345，然后加入。
其他回答
我认为这种区别可以通过SQL中操作的逻辑顺序来最好地解释，这是简化的:
FROM(包括连接) 在哪里 集团 聚合 有 窗口 选择 截然不同的 并，相交，除 命令 抵消 获取
联接不是select语句的子句，而是FROM语句中的操作符。因此，当逻辑处理到达WHERE子句时，属于相应JOIN运算符的所有ON子句在逻辑上“已经发生”。这意味着在LEFT JOIN的情况下，例如，在应用WHERE子句时，外部连接的语义已经发生。
我已经在这篇博文中更深入地解释了下面的例子。运行此查询时:
SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;
LEFT JOIN实际上没有任何有用的效果，因为即使演员没有在电影中演出，演员也会被过滤，因为它的FILM_ID将为NULL, WHERE子句将过滤这样的一行。结果是这样的:
ACTOR_ID FIRST_NAME LAST_NAME COUNT
--------------------------------------
194 MERYL ALLEN 1
198 MARY KEITEL 1
30 SANDRA PECK 1
85 MINNIE ZELLWEGER 1
123 JULIANNE DENCH 1
也就是说，就好像我们内部连接了两个表。如果我们移动ON子句中的筛选谓词，它现在成为外部连接的标准:
SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
AND film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;
这意味着结果将包含没有任何影片的演员，或者没有任何影片且FILM_ID < 10的演员
ACTOR_ID FIRST_NAME LAST_NAME COUNT
-----------------------------------------
3 ED CHASE 0
4 JENNIFER DAVIS 0
5 JOHNNY LOLLOBRIGIDA 0
6 BETTE NICHOLSON 0
...
1 PENELOPE GUINESS 1
200 THORA TEMPLE 1
2 NICK WAHLBERG 1
198 MARY KEITEL 1
简而言之
总是把谓词放在逻辑上最有意义的地方。
为了补充Joel Coehoorn的回答，我将添加一些特定于sqlite的优化信息(其他SQL风格的行为可能不同)。在原来的例子中，LEFT JOIN有不同的结果，这取决于你是否使用JOIN on…在哪里或加入…和。下面是一个稍微修改过的例子:
SELECT *
FROM Orders
LEFT JOIN OrderLines ON Orders.ID = OrderLines.OrderID
WHERE Orders.Username = OrderLines.Username
与
SELECT *
FROM Orders
LEFT JOIN OrderLines ON Orders.ID = OrderLines.OrderID
AND Orders.Username = OrderLines.Username
现在，最初的答案指出，如果使用普通的内部连接而不是左连接，两个查询的结果将是相同的，但执行计划将有所不同。我最近意识到，两者之间的语义差异是，前者强制查询优化器使用与ON子句关联的索引，而后者允许优化器选择ON子句中的任何索引……AND子句，这取决于它认为最有效的方式。
有时，优化器会猜错，您会想强制执行某个执行计划。在本例中，假设SQLite优化器错误地得出结论，认为执行此连接的最快方法是使用Orders上的索引。用户名，当您从经验测试中知道，对订单的索引。ID可以更快地传递查询。
在这种情况下，前者JOIN ON…WHERE语法实际上允许您强制对ID参数执行主连接操作，只有在主连接完成之后才对Username执行辅助过滤。相反，JOIN ON…AND语法允许优化器选择是否在Orders上使用索引。ID或订单。用户名，理论上有可能它会选择一个速度较慢的。
让我们考虑一下这些表格:
A
id | SomeData
B
id | id_A | SomeOtherData
id_A是表a的外键
写这个查询:
SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A;
将提供如下结果:
/ : part of the result
B
+---------------------------------+
A | |
+---------------------+-------+ |
|/////////////////////|///////| |
|/////////////////////|///////| |
|/////////////////////|///////| |
|/////////////////////|///////| |
|/////////////////////+-------+-------------------------+
|/////////////////////////////|
+-----------------------------+
A中有而B中没有的东西意味着B中有空值。
现在，让我们考虑B.id_A中的一个特定部分，并从前面的结果中突出显示它:
/ : part of the result
* : part of the result with the specific B.id_A
B
+---------------------------------+
A | |
+---------------------+-------+ |
|/////////////////////|///////| |
|/////////////////////|///////| |
|/////////////////////+---+///| |
|/////////////////////|***|///| |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+
写这个查询:
SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
AND B.id_A = SpecificPart;
将提供如下结果:
/ : part of the result
* : part of the result with the specific B.id_A
B
+---------------------------------+
A | |
+---------------------+-------+ |
|/////////////////////| | |
|/////////////////////| | |
|/////////////////////+---+ | |
|/////////////////////|***| | |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+
因为这将在内部连接中删除不在B.id_A = SpecificPart中的值
现在，让我们把查询改为这样:
SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
WHERE B.id_A = SpecificPart;
结果是:
/ : part of the result
* : part of the result with the specific B.id_A
B
+---------------------------------+
A | |
+---------------------+-------+ |
| | | |
| | | |
| +---+ | |
| |***| | |
| +---+---+-------------------------+
| |
+-----------------------------+
因为整个结果是根据B. id_a = SpecificPart过滤的，删除了B. id_a为NULL的部分，这些部分在A中，但不在B中
就优化器而言，使用ON或WHERE定义连接子句应该没有区别。
然而，恕我直言，我认为在执行连接时使用ON子句更清楚。这样，您就有了查询的特定部分，该部分规定了如何处理连接，而不是与其余WHERE子句混合。
简短的回答
这取决于连接类型是INNER还是OUTER。
对于INNER JOIN，答案是肯定的，因为INNER JOIN语句可以重写为带有WHERE子句的CROSS JOIN，该子句与您在INNER JOIN查询的ON子句中使用的条件相同。
但是，这只适用于INNER JOIN，不适用于OUTER JOIN。
长回答
考虑到我们有如下的post和post_comment表:
该职位有以下记录:
| id | title |
|----|-----------|
| 1 | Java |
| 2 | Hibernate |
| 3 | JPA |
并且post_comment有以下三行:
| id | review | post_id |
|----|-----------|---------|
| 1 | Good | 1 |
| 2 | Excellent | 1 |
| 3 | Awesome | 2 |
SQL内部连接
SQL JOIN子句允许您关联属于不同表的行。例如，CROSS JOIN将创建一个笛卡尔积(Cartesian Product)，其中包含两个连接表之间所有可能的行组合。
虽然CROSS JOIN在某些场景中很有用，但大多数情况下，您希望基于特定的条件来连接表。这就是INNER JOIN发挥作用的地方。
SQL INNER JOIN允许我们根据通过on子句指定的条件筛选连接两个表的笛卡尔积。
SQL内部连接-对“始终为真”的条件
如果您提供了一个“always true”条件，INNER JOIN将不会过滤连接的记录，结果集将包含两个连接表的笛卡尔积。
例如，如果我们执行下面的SQL INNER JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 1
我们将得到post和post_comment记录的所有组合:
| p.id | pc.id |
|---------|------------|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
因此，如果ON子句条件为"always true"， INNER JOIN就相当于一个CROSS JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 1
ORDER BY p.id, pc.id
SQL INNER JOIN - ON "always false"条件
另一方面，如果On子句条件为“always false”，则所有连接的记录将被过滤掉，结果集将为空。
因此，如果我们执行下面的SQL INNER JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 0
ORDER BY p.id, pc.id
我们不会得到任何结果:
| p.id | pc.id |
|---------|------------|
这是因为上面的查询相当于下面的CROSS JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 0
ORDER BY p.id, pc.id
SQL INNER JOIN - ON子句使用外键和主键列
最常见的ON子句条件是子表中的外键列与父表中的主键列相匹配的条件，如下面的查询所示:
SELECT
p.id AS "p.id",
pc.post_id AS "pc.post_id",
pc.id AS "pc.id",
p.title AS "p.title",
pc.review AS "pc.review"
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id
ORDER BY p.id, pc.id
当执行上面的SQL INNER JOIN查询时，我们得到以下结果集:
| p.id | pc.post_id | pc.id | p.title | pc.review |
|---------|------------|------------|------------|-----------|
| 1 | 1 | 1 | Java | Good |
| 1 | 1 | 2 | Java | Excellent |
| 2 | 2 | 3 | Hibernate | Awesome |
因此，只有匹配ON子句条件的记录才包含在查询结果集中。在我们的例子中，结果集包含了所有的帖子及其post_comment记录。没有关联post_comment的发布行将被排除，因为它们不能满足ON子句条件。
同样，上面的SQL INNER JOIN查询等价于下面的CROSS JOIN查询:
SELECT
p.id AS "p.id",
pc.post_id AS "pc.post_id",
pc.id AS "pc.id",
p.title AS "p.title",
pc.review AS "pc.review"
FROM post p, post_comment pc
WHERE pc.post_id = p.id
未取消的行是满足WHERE子句的行，结果集中只包括这些记录。这是可视化INNER JOIN子句如何工作的最好方法。
| p.id | pc.post_id | pc.id | p.title | pc.review | |------|------------|-------|-----------|-----------| | 1 | 1 | 1 | Java | Good | | 1 | 1 | 2 | Java | Excellent || 1 | 2 | 3 | Java | Awesome || 2 | 1 | 1 | Hibernate | Good || 2 | 1 | 2 | Hibernate | Excellent || 2 | 2 | 3 | Hibernate | Awesome || 3 | 1 | 1 | JPA | Good || 3 | 1 | 2 | JPA | Excellent || 3 | 2 | 3 | JPA | Awesome |
结论
INNER JOIN语句可以重写为带有WHERE子句的CROSS JOIN，该子句与在INNER JOIN查询的ON子句中使用的条件相同。
并不是说这只适用于INNER JOIN，而不适用于OUTER JOIN。
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 我如何循环通过一组记录在SQL Server?
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
- 如何改变字符集(和排序)在整个数据库?
- mySQL:: insert到表，数据从另一个表?
- 如何在Postgres中获得两个字段的MIN() ?
