SQL join:where子句与on子句

sql join where-clause

539787 观看

15回复

31928 作者的声誉

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


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

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

作者: BCS 的来源 发布者: 2008 年 12 月 9 日

回应 15


30

108770 作者的声誉

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

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

作者: matt b 发布者: 2008 年 12 月 9 日

681

301900 作者的声誉

决定

它们不是同一件事。

考虑这些查询:

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

作者: Joel Coehoorn 发布者: 2008 年 12 月 9 日

139

72225 作者的声誉

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

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

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

作者: Cade Roux 发布者: 2008 年 12 月 9 日

8

13525 作者的声誉

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

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

作者: Grant Limberg 发布者: 2008 年 12 月 9 日

36

78884 作者的声誉

我这样做的方式是:

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

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

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

作者: HLGEM 发布者: 2008 年 12 月 9 日

-6

15 作者的声誉

这是我的解决方案。

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得到它的工作。

希望这有帮助。

作者: Le Quang Chien 发布者: 2010 年 4 月 26 日

0

149 作者的声誉

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

作者: Xing-Wei Lin 发布者: 2014 年 1 月 7 日

194

5427 作者的声誉

  • 内连接无关紧要
  • 外连接的事项

    一个。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 | 
作者: Sandeep Jindal 发布者: 2014 年 1 月 7 日

1

21 作者的声誉

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

例如: - '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

希望我已经回答了你的问题。回过头来澄清一下。

作者: Sharon Fernando 发布者: 2014 年 2 月 5 日

0

1 作者的声誉

对于内部联接,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到任何东西(因为“驱动程序”表没有“开”条款),但它是合法的。

作者: Austin Barry 发布者: 2014 年 5 月 16 日

15

53465 作者的声誉

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

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

  1. 如果你总是想要获取左侧行而只是在某些条件匹配时才加入,那么你应该使用ON子句。
  2. 如果要过滤连接双方的产品,则应使用WHERE子句。
作者: Vlad Mihalcea 发布者: 2014 年 5 月 25 日

1

41 作者的声誉

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

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

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

作者: yakoub abaya 发布者: 2014 年 12 月 12 日

7

1649 作者的声誉

当涉及左连接时,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'将具有任何关联的行。

作者: Hrishikesh Mishra 发布者: 2016 年 3 月 13 日

1

58 作者的声誉

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

作者: sree 发布者: 2018 年 2 月 16 日

0

1 作者的声誉

以下是详细说明SQL连接[ https://medium.com/@cdaniel7/run-down-of-sql-joins-434d9d03f2d]

作者: Christopher Daniel Devairakkam 发布者: 2018 年 11 月 6 日
32x32