SQL连接：where子句与on子句

最新推荐文章于 2023-01-16 19:49:52 发布

asdfgh0077

最新推荐文章于 2023-01-16 19:49:52 发布

阅读量1.4k

点赞数

文章标签： sql join where-clause on-clause

原文链接：https://stackoom.com/question/1U6o/SQL连接-where子句与on子句

版权

本文详细讨论了SQL连接中WHERE子句和ON子句的使用和区别，特别是在内外连接中的不同效果。内容涵盖两者的功能、应用场景及性能考虑，强调了在ON子句中定义连接条件有利于提升查询可读性。

摘要由CSDN通过智能技术生成

阅读后，这不是 Explicit与Implicit SQL Joins的重复。答案可能是相关的（甚至是相同的），但问题是不同的。

有什么区别，每个应该有什么区别？

如果我正确理解该理论，则查询优化器应该可以互换使用。

#1楼

我认为这是连接顺序的影响。在左上连接的情况下，SQL先执行左连接，然后再执行where过滤器。在较低的情况下，首先找到Orders.ID = 12345，然后再加入。

#2楼

内连接无关紧要
外连接事项
一种。 WHERE子句：加入之后。加入后将过滤记录。
b。 ON子句-加入之前。记录（来自右表）将在加入之前被过滤。结果可能为空（由于OUTER连接）。

示例：考虑下表：

    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 |

#3楼

在SQL中，“ WHERE”和“ ON”子句是条件状态子，但它们之间的主要区别在于，“选择/更新”语句中使用“ Where”子句来指定条件，而“ 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

希望我回答了你的问题。还原以进行任何澄清。

#4楼

对于内部联接， 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)
...
)

（IMHO）完全使人感到困惑，并且很容易忘记将table1链接到任何内容（因为“ driver”表没有“ on”子句），但这是合法的。

#5楼

本文清楚地说明了区别。它还说明“在on_joind_condition与WHEREjoind_condition或joind_alias为空”。

WHERE子句将FROM子句的结果与JOIN一起过滤，而ON子句用于在FROM和JOIN表之间生成表结果。

如果要产生连接两个表的表结果，则应使用ON子句确定如何联接表。当然，例如，在INNER JOIN的情况下，这也可以从原始表中过滤行。
如果要过滤连接双方的乘积，则应使用WHERE子句。

#6楼

这是我的解决方案。

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

希望能有所帮助。

#7楼

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

因此，如果您所依据的列不是那些索引列之一，那么我怀疑将其保留在WHERE中会更好。

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

#8楼

在内部联接上，它们的含义相同。但是，如果将联接条件放在WHERE vs ON子句中，则在外部联接中将得到不同的结果。看看这个相关的问题和答案（由我自己）。

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

#9楼

它们不是同一件事。

考虑以下查询：

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

#10楼

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

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

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

#11楼

对于优化器而言，无论是使用ON还是WHERE定义连接子句都不会有所不同。

但是，恕我直言，我认为在执行连接时使用ON子句更加清晰。这样，您在查询中就有一个特定的部分，该部分指示如何处理联接以及如何与其他WHERE子句混合。

#12楼

我这样做的方式是：

如果要执行INNER JOIN则始终将连接条件放在ON子句中。因此，请勿在ON子句中添加任何WHERE条件，而应将它们放在WHERE子句中。
如果要进行LEFT JOIN ，则将任何WHERE条件添加到LEFT JOIN 右侧表的ON子句中。这是必须的，因为添加引用联接右侧的WHERE子句会将联接转换为INNER JOIN。
当您要查找不在特定表中的记录时，就是一个例外。您可以通过以下方式将对RIGHT JOIN表中的唯一标识符（永远不会为NULL）的引用添加到WHERE子句中： WHERE t2.idfield IS NULL 。因此，唯一应在联接右侧引用表的地方是查找那些不在表中的记录。

#13楼

在左连接方面， 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    |       |
+-------+-------------+------+-----+---------+-------+

表t2的ID为fid。

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'将具有任何关联的行。

#14楼

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

#15楼

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

FROM （包括联接）
WHERE
GROUP BY
集合体
HAVING
WINDOW
SELECT
DISTINCT
UNION ， INTERSECT ， EXCEPT
ORDER BY
OFFSET
FETCH

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

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

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将为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 < 10电影的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

简而言之

从逻辑上讲，请始终将谓词放在最有意义的位置。

#16楼

让我们考虑一下这些表：

一种

id | SomeData

乙

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中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过滤了整个结果， B.id_A = SpecificPart删除了B中没有的部分B.id_A = NULL 。

#17楼

您是要联接数据还是过滤数据？

为了提高可读性，将这些用例分别隔离在ON和WHERE上是最有意义的。

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

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

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

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

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

asdfgh0077

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫