SQLJOIN-WHERE子句vs. ON子句

在内连接上，它们的意思是一样的。但是，根据您是否将连接条件放在WHERE与ON子句中，您将在外连接中获得不同的结果。看看这个相关问题和这个答案（由我）。

我认为习惯于将连接条件放在ON子句中是最有意义的（除非它是一个外连接，并且您确实希望它在where子句中），因为它使任何阅读您的查询的人都能更清楚地了解表正在连接的条件，并且它有助于防止WHERE子句长达数十行。

它们不是一回事。

考虑这些查询：

SELECT *FROM OrdersLEFT JOIN OrderLines ON OrderLines.OrderID=Orders.IDWHERE Orders.ID = 12345

和

SELECT *FROM OrdersLEFT JOIN OrderLines ON OrderLines.OrderID=Orders.IDAND Orders.ID = 12345

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

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

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

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

我把它们放在任何地方，取决于易读性。

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

然而，恕我直言，我认为在执行连接时使用ON子句要清楚得多。这样，您就有了查询的特定部分，它决定了如何处理连接，而不是与其余WHERE子句混合。

我这样做的方式是：

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

• 如果您正在执行LEFT JOIN，请将任何WHERE条件添加到连接对侧表的ON子句中。这是必须的，因为添加引用连接右侧的WHERE子句将把连接转换为INNER JOIN。

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

这就是我的解决方案。

SELECT song_ID,songs.fullname, singers.fullnameFROM music JOIN songs ON songs.ID = music.song_IDJOIN singers ON singers.ID = music.singer_IDGROUP BY songs.fullname

你必须有GROUP BY让它工作。

希望这个帮助。

我认为这是连接序列效果。在左上角连接的情况下，SQL先执行左连接，然后执行where过滤器。在Downer情况下，先找到Orders.ID=12345，然后再加入。

• 对于内部连接无关紧要

• 外连接事项

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

  b.ON子句-之前连接。记录（来自右表）将在连接前被过滤。这可能在结果中以null结束（因为OUTER连接）。

示例：考虑以下表格：

1. 文档：

   id	name
   1	Document1
   2	Document2
   3	Document3
   4	Document4
   5	Document5

2. 下载：

   id	document_id	用户名
   1	1	sanDeep
   2	1	simi
   3	2	sanDeep
   4	2	reya
   5	3	simi

a）在WHERE子句中：

   SELECT documents.name, downloads.idFROM documentsLEFT OUTER JOIN downloadsON documents.id = downloads.document_idWHERE username = 'sandeep'

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

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

b）内部JOIN子句

   SELECT documents.name, downloads.idFROM documentsLEFT OUTER JOIN downloadsON documents.id = downloads.document_idAND username = 'sandeep'

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

请注意，documents中与两个条件都不匹配的行是如何用NULL值填充的。

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

在SQL中，“WHERE”和“ON”子句是一种条件语句，但它们之间的主要区别在于，“WHERE”子句用于选择/更新语句中指定条件，而“ON”子句用于连接，在连接表之前，它验证或检查目标表和源表中的记录是否匹配

例如：-'WHERE'

SELECT * FROM employee WHERE employee_id=101

例如：-'ON'

有两个表雇员和employee_details，匹配的列employee_id。

SELECT * FROM employeeINNER JOIN employee_detailsON employee.employee_id = employee_details.employee_id

希望我已经回答了你的问题。#36825；求澄清

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

update mytableset myscore=100where exists (select 1 from table1inner join table2on (table2.key = mytable.key)inner join table3on (table3.key = table2.key and table3.key = table1.key)...)

这（IMHO）完全让人困惑，很容易忘记将table1链接到任何东西（因为“驱动程序”表没有“on”子句），但这是合法的。

简短的回答

这取决于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内部加入

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

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

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

SQLINNER JOIN-ON“始终为真”条件

如果您提供“始终为真”条件，则INNER JOIN不会过滤连接的记录，结果集将包含两个连接表的笛卡尔乘积。

例如，如果我们执行以下SQLINNER JOIN查询：

SELECTp.id AS "p.id",pc.id AS "pc.id"FROM post pINNER 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查询：

SELECTp.id AS "p.id",pc.id AS "pc.id"FROM post pCROSS JOIN post_commentWHERE 1 = 1ORDER BY p.id, pc.id

SQLINNER JOIN-ON“始终为假”条件

另一方面，如果ON子句条件为“始终为假”，则所有连接的记录都将被过滤掉，结果集将为空。

因此，如果我们执行以下SQLINNER JOIN查询：

SELECTp.id AS "p.id",pc.id AS "pc.id"FROM post pINNER JOIN post_comment pc ON 1 = 0ORDER BY p.id, pc.id

我们不会得到任何结果：

| p.id    | pc.id      ||---------|------------|

这是因为上面的查询等价于以下CROSS JOIN查询：

SELECTp.id AS "p.id",pc.id AS "pc.id"FROM post pCROSS JOIN post_commentWHERE 1 = 0ORDER BY p.id, pc.id

SQLINNER JOIN-ON子句使用外键和主键列

最常见的ON子句条件是将子表中的外键列与父表中的主键列匹配的条件，如以下查询所示：

SELECTp.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 pINNER JOIN post_comment pc ON pc.post_id = p.idORDER BY p.id, pc.id

执行上述SQLINNER 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子句条件。

同样，上述SQLINNER JOIN查询等效于以下CROSS JOIN查询：

SELECTp.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 pcWHERE 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子句中使用的条件相同。

并不是说这只适用于内部连接，而不适用于外部连接。

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

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

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

当涉及到左连接时，其中子句与关于条款之间存在很大差异。

下面是例子：

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. v='K'，第一个查询从t1返回一条记录，从t2返回其依赖行（如果有）。

第二个查询从t1返回行，但只有t1. v='K'才会有任何与之关联的行。

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

我认为这种区别可以通过SQL中操作的逻辑顺序来最好地解释，即简化：

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

连接不是选择语句的子句，而是FROM内部的运算符。因此，当逻辑处理到达WHERE子句时，属于相应JOIN运算符的所有ON子句都“已经发生”逻辑上。这意味着在LEFT JOIN的情况下，例如，在应用WHERE子句时，外部连接的语义学已经发生。

我在这篇博客文章中更深入地解释了以下示例。运行此查询时：

SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)FROM actor aLEFT JOIN film_actor fa ON a.actor_id = fa.actor_idWHERE film_id < 10GROUP BY a.actor_id, a.first_name, a.last_nameORDER BY count(fa.film_id) ASC;

LEFT JOIN实际上没有任何有用的效果，因为即使演员没有在电影中扮演，演员也会被过滤，因为它的FILM_ID将是NULL，WHERE子句将过滤这样的行。结果类似于：

ACTOR_ID  FIRST_NAME  LAST_NAME  COUNT--------------------------------------194       MERYL       ALLEN      1198       MARY        KEITEL     130        SANDRA      PECK       185        MINNIE      ZELLWEGER  1123       JULIANNE    DENCH      1

即。就像我们内部连接两个表一样。如果我们移动ON子句中的过滤器谓词，它现在成为外部连接的条件：

SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)FROM actor aLEFT JOIN film_actor fa ON a.actor_id = fa.actor_idAND film_id < 10GROUP BY a.actor_id, a.first_name, a.last_nameORDER BY count(fa.film_id) ASC;

这意味着结果将包含没有任何电影的演员，或者没有任何FILM_ID < 10的电影

ACTOR_ID  FIRST_NAME  LAST_NAME     COUNT-----------------------------------------3         ED          CHASE         04         JENNIFER    DAVIS         05         JOHNNY      LOLLOBRIGIDA  06         BETTE       NICHOLSON     0...1         PENELOPE    GUINESS       1200       THORA       TEMPLE        12         NICK        WAHLBERG      1198       MARY        KEITEL        1

总之

总是把你的谓词放在逻辑上最有意义的地方。

让我们看看这些表格：

一个

id | SomeData

B

id | id_A | SomeOtherData

id_A是表A的外键

编写此查询：

SELECT *FROM ALEFT JOIN BON A.id = B.id_A;

将提供此结果：

/ : part of the resultB+---------------------------------+A         |                                 |+---------------------+-------+                         ||/////////////////////|///////|                         ||/////////////////////|///////|                         ||/////////////////////|///////|                         ||/////////////////////|///////|                         ||/////////////////////+-------+-------------------------+|/////////////////////////////|+-----------------------------+

在A中但不在B中的内容意味着B有空值。

现在，让我们考虑B.id_A中的一个特定部分，并从上一个结果中突出显示它：

/ : part of the result* : part of the result with the specific B.id_AB+---------------------------------+A         |                                 |+---------------------+-------+                         ||/////////////////////|///////|                         ||/////////////////////|///////|                         ||/////////////////////+---+///|                         ||/////////////////////|***|///|                         ||/////////////////////+---+---+-------------------------+|/////////////////////////////|+-----------------------------+

编写此查询：

SELECT *FROM ALEFT JOIN BON A.id = B.id_AAND B.id_A = SpecificPart;

将提供此结果：

/ : part of the result* : part of the result with the specific B.id_AB+---------------------------------+A         |                                 |+---------------------+-------+                         ||/////////////////////|       |                         ||/////////////////////|       |                         ||/////////////////////+---+   |                         ||/////////////////////|***|   |                         ||/////////////////////+---+---+-------------------------+|/////////////////////////////|+-----------------------------+

因为这会在内连接中删除不在B.id_A = SpecificPart中的值

现在，让我们将查询更改为：

SELECT *FROM ALEFT JOIN BON A.id = B.id_AWHERE B.id_A = SpecificPart;

现在的结果是：

/ : part of the result* : part of the result with the specific B.id_AB+---------------------------------+A         |                                 |+---------------------+-------+                         ||                     |       |                         ||                     |       |                         ||                     +---+   |                         ||                     |***|   |                         ||                     +---+---+-------------------------+|                             |+-----------------------------+

因为整个结果是根据B.id_A = SpecificPart过滤的，删除了不在B中的A中的B.id_A IS NULL部分

您正在尝试加入数据还是过滤数据？

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

• 在ON中加入数据
• 在WHERE中过滤数据

读取WHERE子句中存在JOIN条件和过滤条件的查询可能会变得非常困难。

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

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

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

关于你的问题，

只要您的服务器可以获取，内连接上的“on”或“where”都是相同的：

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

和

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

不是所有的口译员都知道“在哪里”这个选项，所以也许应该避免。当然，“on”条款更清楚。

相当，字面意思。

在大多数开源数据库（最值得注意的例子是mysql和postgresql）中，查询规划是关系数据库管理系统中的访问路径选择（Selinger et al，1979）中出现的经典算法的变体。在这种方法中，条件有两种类型

• 引用单个表的条件（用于过滤）
• 引用两个表的条件（被视为连接条件，与它们出现的地方无关）

特别是在MySql中，您可以看到自己，通过跟踪优化器，join .. on条件由等效的where条件变为解析过程中替换。类似的事情发生在postgresql中（尽管无法通过日志查看它，您必须阅读源描述）。

不管怎样，主要的一点是，两个语法变体失去在解析/查询重写阶段的区别，甚至没有到达查询规划和执行阶段。因此，毫无疑问，它们在性能方面是否相等，它们在达到执行阶段之前就变得一模一样了。

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

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

显然，这两种语法样式不是相当于外连接，对于那些您必须使用join ... on语法的语法

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

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

为了添加到Joel Coehoorn的响应中，我将添加一些特定于sqlite的优化信息（其他SQL风格的行为可能不同）。在原始示例中，LEFT JOIN根据您使用的是JOIN ON ... WHERE还是JOIN ON ... AND而有不同的结果。这是一个稍微修改的示例来说明：

SELECT *FROM OrdersLEFT JOIN OrderLines ON Orders.ID = OrderLines.OrderIDWHERE Orders.Username = OrderLines.Username

对

SELECT *FROM OrdersLEFT JOIN OrderLines ON Orders.ID = OrderLines.OrderIDAND Orders.Username = OrderLines.Username

现在，最初的答案指出，如果您使用纯内连接而不是左连接，两个查询的结果将是相同的，但执行计划会有所不同。我最近意识到，两者之间的语义差异在于，前者部队查询优化器使用与ON子句关联的索引，而后者允许优化器根据它认为最有效的方式选择ON ... AND子句中的任何索引。

有时，优化器会猜错，你会想要强制执行某个执行计划。在这种情况下，假设SQLite优化器错误地得出结论，执行此连接的最快方法是使用Orders.Username上的索引，而你从经验测试中知道Orders.ID上的索引会更快地交付你的查询。

在这种情况下，以前的JOIN ON ... WHERE语法本质上允许您对ID参数进行力主连接操作，仅在主连接完成后对Username进行二次过滤。相比之下，JOIN ON ... AND语法允许优化器选择是在Orders.ID还是Orders.Username上使用索引，理论上它有可能选择最终速度较慢的索引。