SQL JOIN：WHERE 子句和 ON 子句有什么区别？

Question

有什么区别以及每个内容应该包含什么？

如果我正确理解这个理论，查询优化器应该能够互换使用两者。

（注意：这个问题不是显式与隐式 SQL 连接的重复。 答案可能相关（甚至相同），但问题不同。）

Answer 1

它们不是同一件事。

考虑以下查询：

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

第一个查询

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

将返回订单号 12345 的订单及其行（如果有）。
第二个将返回所有订单，但只有订单 12345 才会有与其关联的任何行。

对于INNER JOIN，子句实际上是等价的。 然而，仅仅因为它们在功能上相同，即它们产生相同的结果，并不意味着这两种子句具有相同的语义。

Answer 2

• 对于内部连接不重要

• 对于外部联接重要

  a. WHERE 子句：连接发生后将过滤记录。

  b. ON 子句：右表中的记录将在加入之前进行过滤。 这可能最终在结果中为 null（因为 OUTER 连接）。

示例：考虑以下两个表格：

1. 文档：

   <表类=“s-表”>
   <标题>

   id
   名称

   
   <正文>

   1
   文档1

   2
   文档2

   3
   文档3

   4
   文档4

   5
   文档5

2. 下载：

   <表类=“s-表”>
   <标题>

   id
   document_id
   用户名

   
   <正文>

   1
   1
   桑深

   2
   1
   西米

   3
   2
   桑深

   4
   2
   雷亚

   5
   3
   西米

a) 在 WHERE 子句中：

   SELECT documents.name, downloads.id
     FROM documents
     LEFT OUTER JOIN downloads
       ON documents.id = downloads.document_id
     WHERE username = 'sandeep'

对于上面的查询，中间连接表将如下所示。

id(来自文档)	name	id (来自下载)	document_id	用户名
1	Document1	1	1	sandeep
1	Document1	2	1	simi
2	Document2	3	2	sandeep
2	Document2	4	2	reya
3	Document3	5	3	simi
4	Document4	NULL	NULL	NULL
5	Document5	NULL	NULL	NULL

应用后WHERE 子句并选择列出的属性，结果将是：

name	id
Document1	1
Document2	3

b) Inside JOIN 子句

   SELECT documents.name, downloads.id
   FROM documents
     LEFT OUTER JOIN downloads
       ON documents.id = downloads.document_id
         AND username = 'sandeep'

对于上面的查询，中间连接表将如下所示。

id（来自文档）	name	id（来自下载）	document_id	用户名
1	Document1	1	1	sandeep
2	Document2	3	2	sandeep
3	Document3	NULL	NULL	NULL
4	Document4	NULL	NULL	NULL
5	Document5	NULL	NULL	NULL

注意 documents 与两个条件都不匹配的将使用 NULL 值填充。

选择列出的属性后，结果将是：

name	id
Document1	1
Document2	3
Document3	NULL
Document4	NULL
Document5	NULL

Answer 3

在 INNER JOIN 上，它们是可以互换的，优化器将随意重新排列它们。

在 OUTER JOIN 上，它们不一定可以互换，具体取决于它们依赖于连接的哪一侧。

我根据可读性将它们放在任一位置。

Answer 4

我的做法是：

• 如果您正在执行INNER JOIN，则始终将连接条件放在ON 子句中。 因此，不要向 ON 子句添加任何 WHERE 条件，而应将它们放在 WHERE 子句中。

• 如果您要执行LEFT JOIN，请将任何 WHERE 条件添加到右表中的 ON 子句< /strong> 连接一侧。 这是必须的，因为添加引用联接右侧的 WHERE 子句会将联接转换为 INNER JOIN。

  例外情况是当您查找不在特定表中的记录时。 您可以通过以下方式将对 RIGHT JOIN 表中的唯一标识符（永远不会为 NULL）的引用添加到 WHERE 子句：WHERE t2.idfield IS NULL。 因此，您应该在连接右侧引用表的唯一时间是查找表中没有的记录。

Answer 5

在内部联接上，它们的含义相同。 但是，根据是否将连接条件放在 WHERE 与 ON 子句中，您将在外连接中得到不同的结果。 查看此相关问题并这个答案（由我） 。

我认为养成始终将联接条件放在 ON 子句中的习惯是最有意义的（除非它是外部联接并且您实际上确实希望将其放在 where 子句中），因为它使任何阅读您的查询的人都更清楚表的连接条件是什么，它还有助于防止 WHERE 子句长达数十行。

Answer 6

简短回答

这取决于 JOIN 类型是 INNER 还是 OUTER。

对于 INNER JOIN，答案是肯定的，因为 INNER JOIN 语句可以重写为 CROSS JOIN，其中的 WHERE 子句与 INNER JOIN 查询的 ON 子句中使用的条件相同。

但是，这仅适用于 INNER JOIN，不适用于 OUTER JOIN。

长答案

考虑到我们有以下 post 和 post_comment 表：

post 具有以下记录：

| id | title     |
|----|-----------|
| 1  | Java      |
| 2  | Hibernate |
| 3  | JPA       |

并且post_comment 具有以下三行：

| id | review    | post_id |
|----|-----------|---------|
| 1  | Good      | 1       |
| 2  | Excellent | 1       |
| 3  | Awesome   | 2       |

SQL INNER JOIN

SQL JOIN 子句允许您关联属于不同表的行。 例如， CROSS JOIN 将创建一个笛卡尔积，其中包含两者之间所有可能的行组合连接表。

虽然交叉连接在某些情况下很有用，但大多数时候，您希望根据特定条件连接表。 这就是 INNER JOIN 发挥作用的地方。

SQL INNER JOIN 允许我们根据 ON 子句指定的条件过滤连接两个表的笛卡尔积。

SQL INNER JOIN - ON“始终为真”条件

如果提供“始终为真”条件，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 子句条件为“始终为真” ，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 及其 post_comment 记录。 没有关联 post_comment 的 post 行将被排除，因为它们无法满足 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 语句可以重写为 CROSS JOIN，其中的 WHERE 子句与 INNER JOIN 查询的 ON 子句中使用的条件相同。

并不是说这仅适用于 INNER JOIN，不适用于 OUTER JOIN。

Answer 7

让我们考虑这些表：

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 进行过滤的删除 A 中不属于 B 的部分 B.id_A IS NULL

Answer 8

在左连接方面，where 子句 与 on 子句 之间存在很大差异。

例如：

mysql> desc t1; 
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| fid   | int(11)     | NO   |     | NULL    |       |
| v     | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

fid 是表 t2 的 id。

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| v     | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

“on 子句”查询：

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K' 
    -> ;
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  1 |   1 | H | NULL | NULL |
|  2 |   1 | B | NULL | NULL |
|  3 |   2 | H | NULL | NULL |
|  4 |   7 | K | NULL | NULL |
|  5 |   5 | L | NULL | NULL |
+----+-----+---+------+------+
5 rows in set (0.00 sec)

“where 子句”查询：

mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K';
+----+-----+---+------+------+
| id | fid | v | id   | v    |
+----+-----+---+------+------+
|  4 |   7 | K | NULL | NULL |
+----+-----+---+------+------+
1 row in set (0.00 sec)

很明显，
第一个查询从 t1 中返回一条记录，并从 t2 中返回其从属行（如果有），其中行 t1.v = 'K'。

第二个查询返回 t1 中的行，但仅当 t1.v = 'K' 时才会有与之关联的行。

Answer 9

就优化器而言，无论您使用 ON 还是 WHERE 定义连接子句，都应该没有什么区别。

然而，恕我直言，我认为在执行连接时使用 ON 子句会更清楚。 这样，您就有了查询的特定部分，该部分指示如何处理联接以及如何与其余 WHERE 子句混合。

Answer 10

您是否正在尝试连接数据或过滤数据？

为了便于阅读，将这些用例分别隔离到 ON 和 WHERE 是最有意义的。

• ON 中的连接数据
• WHERE 中的过滤数据

如果 WHERE 子句中存在 JOIN 条件和过滤条件，则读取查询会变得非常困难。

从性能角度来看，您不应该看到差异，尽管不同类型的 SQL 有时会以不同的方式处理查询计划，因此值得尝试 ¯\_(ツ)_/¯ （请注意缓存对查询的影响）速度）

另外正如其他人所指出的，如果您使用外连接，如果您将过滤条件放在 ON 子句中，您将得到不同的结果，因为它只影响其中一个表。

我在这里写了一篇更深入的文章：
https://dataschool.com/learn/difference- Between-where -and-on-in-sql

Answer 11

我认为这种区别最好通过 SQL 中操作的逻辑顺序，简化为：

• FROM（包括连接）
• WHERE
• GROUP BY
• 聚合
• HAVING
• WINDOW
• SELECT
• DISTINCT
• UNION, INTERSECT, EXCEPT
• ORDER BY
• OFFSET
• FETCH

连接不是子句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 code> 将为 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 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

简而言之，

始终将谓词放在逻辑上最有意义的地方。

Answer 12

在 SQL 中，'WHERE' 和 'ON' 子句都是条件语句，但它们之间的主要区别是，'Where' 子句用于 Select/Update 语句中指定条件，而 'ON' 子句用于指定条件。用于连接，在连接表之前验证或检查目标表和源表中的记录是否匹配

例如： - 'WHERE'

SELECT * FROM employee WHERE employee_id=101

例如： - 'ON '

有两个表employee 和employee_details，匹配的列是employee_id。

SELECT * FROM employee 
INNER JOIN employee_details 
ON employee.employee_id = employee_details.employee_id

希望我已经回答了您的问题。
返回以获得任何澄清。

Answer 13

我认为这是连接序列效应。
在左上连接的情况下，SQL先做左连接，然后再做where过滤。
在downer的情况下，先找到Orders.ID=12345，然后进行join。

Answer 14

对于内部联接，WHERE 和 ON 可以互换使用。 事实上，可以在相关子查询中使用ON。 例如：（

update mytable
set myscore=100
where exists (
select 1 from table1
inner join table2
on (table2.key = mytable.key)
inner join table3
on (table3.key = table2.key and table3.key = table1.key)
...
)

恕我直言）这对人类来说完全令人困惑，并且很容易忘记将 table1 链接到任何东西（因为“driver”表没有“on”子句），但这是合法的。

Answer 15

通常，一旦两个表已经连接，就会在 WHERE 子句中处理过滤。 不过，您可能希望在连接一个或两个表之前过滤它们。
即，where 子句适用于整个结果集，而 on 子句仅适用于相关连接。

Answer 16

从字面上看，它们是等效的。

在大多数开源数据库中（最著名的例子是MySql和postgresql），查询计划是出现在访问路径选择中的经典算法的变体。关系数据库管理系统（Selinger 等人，1979）。 在此方法中，条件有两种类型

• 条件引用单个表（用于过滤）
• 条件引用两个表（视为连接条件，无论出现在何处 >)

特别是在 MySql 中，通过跟踪优化器，您可以看到自己，join .. on 条件在解析期间被替换为等效的 where 条件。 postgresql 中也发生类似的事情（虽然没有办法通过日志查看，必须阅读源码描述）。

无论如何，要点是，两种语法变体之间的差异在解析/查询重写阶段就消失了，甚至没有到达查询计划和执行阶段。 因此，毫无疑问它们在性能方面是否等效，它们在到达执行阶段之前很久就变得相同。

您可以使用explain来验证它们是否生成相同的计划。 例如，在 postgres 中，计划将包含 join 子句，即使您没有在任何地方使用 join..on 语法。

Oracle 和 SQL Server 不是开源的，但据我所知，它们基于等价规则（类似于关系代数中的规则），并且在这两种情况下也会产生相同的执行计划。

显然，对于外连接，这两种语法样式并不等效，对于那些您必须使用 join ... on 语法

Answer 17

为了获得更好的性能，表应该有一个特殊的索引列用于 JOINS 。

因此，如果您所条件的列不是这些索引列之一，那么我怀疑最好将其保留在 WHERE 中。

因此，您使用索引列进行 JOIN，然后在 JOIN 之后对无索引列运行条件。

Answer 18

A。 WHERE子句：加入后，记录将被过滤。

b. ON 子句 - 在加入之前，记录（来自右表）将被过滤。

Answer 19

为了添加 Joel Coehoorn 的响应，我将添加一些特定于 sqlite 的优化信息（其他 SQL 风格可能表现不同）。 在原始示例中，LEFT JOIN 会产生不同的结果，具体取决于您使用的是 JOIN ON ... WHERE 还是 JOIN ON ... AND。 下面是一个稍作修改的示例来说明：

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.Username 上的索引，而您从经验测试得知 上的索引>Orders.ID 会更快地交付您的查询。

在这种情况下，以前的 JOIN ON ... WHERE 语法本质上允许您强制在 ID 参数上发生主连接操作，仅在主连接完成后才对用户名执行二次过滤。 相比之下，JOIN ON ... AND 语法允许优化器选择是否使用 Orders.ID 或 Orders.Username 上的索引，并且理论上有可能它会选择最终速度较慢的那个。

Answer 20

关于你的问题，

只要你的服务器可以得到它，内连接上的“on”或“where”都是相同的：

select * from a inner join b on a.c = b.c

并且

select * from a inner join b where a.c = b.c

“where”选项并非所有解释器都知道，所以也许应该避免。 当然，“on”子句更加清晰。

Answer 21

重要的是：
看看例如，
这是当您在末尾使用 WHERE 子句时
其中 cat.category 为 null 或 cat.category <> “其他”

and here you are using AND clause on join

category 'OTHER' or category is null (I don't know why it doesn't show not equal sign)

Since when you are joining it you are taking the filtred value as a NULL

Answer 22

这是我的解决方案。

SELECT song_ID,songs.fullname, singers.fullname
FROM music JOIN songs ON songs.ID = music.song_ID  
JOIN singers ON singers.ID = music.singer_ID
GROUP BY songs.fullname

您必须拥有GROUP BY才能使其正常工作。

希望这有帮助。