提问



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





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


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

最佳参考


它们不是同一件事。


考虑这些查询:


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

其它参考1



  • 内连接无关紧要

  • 外连接的事项


    一个。 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 | 

其它参考2


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


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


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

其它参考3


我这样做的方式是:


始终将连接条件放在on子句中
如果您正在进行内部联接,那么不要在on子句中添加任何where条件,将它们放在where子句中


如果要进行左连接,请将任何where条件添加到连接右侧表的on子句中。这是必须的,因为添加引用连接右侧的where子句会将连接转换为内连接(下面描述了一个例外)。


例外情况是,当您查找不在特定表中的记录时,您将在右连接表中将引用添加到唯一标识符(不是永久为空),以这种方式将其添加到where子句where t2。 idfield为null。因此,您应该在连接右侧引用表的唯一时间是查找不在表中的那些记录。

其它参考4


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


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

其它参考5


本文清楚地解释了差异。它还解释了ON joined_condition vs WHERE joined_condition或joined_alias为null。[29]


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



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

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


其它参考6


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


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

其它参考7


当涉及左连接时, 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


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


因此,如果您所关注的列不是那些索引列之一,那么我怀疑最好将它保存在WHERE中。


所以你使用索引列加入,然后在JOIN之后你在无索引列上运行条件。

其它参考9


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

其它参考10


我认为这是连接序列效应。
在左上角连接的情况下,SQL首先执行左连接,然后执行筛选。
在downer的情况下,首先找到Orders.ID=12345,然后再加入。

其它参考11


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


例如: - WHERE



  

    SELECT * FROM employee WHERE employee_id=101

  



例如: - 开启


*有两个表employee和employee_details,匹配列是employee_id。*



  

    SELECT * FROM员工
      INNER JOIN employee_details 开启 employee.employee_id=employee_details.employee_id

  



希望我已回答你的问题。回复澄清。

其它参考12


对于内连接,WHEREON可以互换使用。实际上,可以在相关子查询中使用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链接到任何东西(因为驱动程序表没有on条款),但它是法律。

其它参考13


这是我的解决方案。


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才能使其正常工作。


希望这有帮助。