SQL join:where子句与on子句

阅读之后,这不是Explicit vs Implicit SQL Joins的重复.答案可能是相关的(甚至是相同的),但问题是不同的.

---

有什么区别,应该分别做些什么？

如果我理解正确的理论,查询优化器应该能够互换使用.

1> Joel Coehoor..：

---

它们不是同一件事.

考虑这些查询:

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,条款实际上是等价的.然而,仅仅因为它们在功能上是相同的,因为它们产生相同的结果,并不意味着这两种子句具有相同的语义含义.

---

@FistOfFury Sql Server使用查询优化器过程来编译和评估您的代码,以产生最佳的执行计划.它并不完美,但大部分时间都没关系,你会得到相同的执行计划.

---

通过将where子句放在内部连接的"on"子句中,您会获得更好的性能吗？

---

在Postgres中,我注意到它们并不等同,导致了不同的查询计划.如果使用ON,则会导致使用实现.如果您使用WHERE,则使用哈希.具体化的情况比哈希的成本高10倍.这是使用一组ID而不是一个ID.

---

@JamesHutchison很难根据观察到的行为进行可靠的性能概括.有一天真实的情况往往是错误的,因为这是一个实现细节而不是记录的行为.数据库团队一直在寻找提高优化器性能的地方.如果ON行为没有改善以匹配WHERE,我会感到惊讶.除了"一般性能改进"之外,它可能甚至不会出现在版本到版本的任何地方.

---

@FiHoran这不是Sql Server的工作方式.当统计数据显示它有用时,它将根据WHERE子句中的项进行积极的预过滤.

2> Sandeep Jind..：

---

内连接无关紧要

外连接的事项

一个.WHERE条款:后加入.加入发生后,将过滤记录.

湾 ON条款 - 加入之前.在加入之前将过滤记录(来自右表).这可能最终在结果中为null(因为OUTER join).

示例:请考虑以下表格:

    1. documents:
     | id    | name        |
     --------|-------------|
     | 1     | Document1   |
     | 2     | Document2   |
     | 3     | Document3   |
     | 4     | Document4   |
     | 5     | Document5   |


    2. downloads:
     | id   | document_id   | username |
     |------|---------------|----------|
     | 1    | 1             | sandeep  |
     | 2    | 1             | simi     |
     | 3    | 2             | sandeep  |
     | 4    | 2             | reya     |
     | 5    | 3             | simi     |

a)内WHERE条款:

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

 For above query the intermediate join table will look like this.

    | id(from documents) | name         | id (from downloads) | document_id | username |
    |--------------------|--------------|---------------------|-------------|----------|
    | 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     |

  After applying the `WHERE` clause and selecting the listed attributes, the result will be: 

   | name         | id |
   |--------------|----|
   | Document1    | 1  |
   | Document2    | 3  | 

b)内部JOIN条款

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

For above query the intermediate join table will look like this.

    | id(from documents) | name         | id (from downloads) | document_id | username |
    |--------------------|--------------|---------------------|-------------|----------|
    | 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     |

Notice how the rows in `documents` that did not match both the conditions are populated with `NULL` values.

After Selecting the listed attributes, the result will be: 

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

---

IMO这是最好的答案,因为它清楚地展示了其他流行答案的"幕后".

3> Cade Roux..：

---

在INNER JOINs上它们是可互换的,优化器将随意重新排列它们.

在OUTER JOINs上,它们不一定是可互换的,这取决于它们所依赖的连接的哪一侧.

我根据可读性将它们放在任何一个地方.

---

[在SQL Server中有一个边缘情况,对于内部连接,它确实有所作为](http://stackoverflow.com/questions/4694281/is-it-better-to-do-an-equi-join-in-的从 - 子句 - 或-where子句/ 7967048#7967048)

4> HLGEM..：

---

我这样做的方式是:

ON如果你正在做的话,总是把连接条件放在子句中INNER JOIN.因此,不要向ON子句添加任何WHERE条件,将它们放在WHERE子句中.

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

例外情况是您查找不在特定表中的记录.您可以通过以下方式将对RIGHT JOIN表中的唯一标识符(不是NULL)的引用添加到WHERE子句中:WHERE t2.idfield IS NULL.因此,您应该在连接的右侧引用表的唯一时间是查找不在表中的那些记录.

---

到目前为止,这是我读过的最好的答案.一旦你的大脑理解左连接**将返回左表中的所有行并且你必须稍后过滤它,那么完全有道理.

5> matt b..：

---

在内连接上,它们意味着同样的事情.但是,在外连接中将获得不同的结果,具体取决于是否将连接条件放在WHERE与ON子句中.看看这个相关的问题和这个答案(由我).

我认为最常见的做法是始终将连接条件放在ON子句中(除非它是外部连接,并且实际上确实需要在where子句中),因为它使任何读取查询的人都更清楚这些表的连接条件是什么,它还有助于防止WHERE子句长达数十行.

6> Vlad Mihalce..：

---

本文清楚地解释了差异.它还解释了"ON joined_condition vs WHERE joined_condition或joined_alias为null".

WHERE子句过滤JOIN的左侧和右侧,而ON子句将始终仅过滤右侧.

如果你总是想要获取左侧行而只是在某些条件匹配时才加入,那么你应该使用ON子句.

如果要过滤连接双方的产品,则应使用WHERE子句.

7> Hrishikesh M..：

---

当涉及左连接时,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.v ='K'返回来自t1的记录及其从t2的依赖行(如果有的话).

第二个查询从t1返回行,但仅对于t1.v ='K'将包含任何关联的行.

8> Grant Limber..：

---

就优化器而言,无论是使用ON还是WHERE定义join子句,都不应该有所区别.

但是,恕我直言,我认为在执行连接时使用ON子句要清楚得多.这样,您有一个特定的查询部分,它指示如何处理连接而不是与其余的WHERE子句混合.