首页 文章

SQL join:where子句与on子句

提问于
浏览
532

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


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

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

14 回答

  • 30

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

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

  • 16

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

    For Example: - 'WHERE'

    SELECT * FROM employee WHERE employee_id = 101

    For Example: - 'ON'

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

    SELECT * FROM employee INNER JOIN employee_details ON employee.employee_id = employee_details.employee_id

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

  • -6

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

  • 139

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

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

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

  • 208

    在内连接上,它们的意思相同 . 但是,根据是否将连接条件放在WHERE与ON子句中,您将在外连接中获得不同的结果 . 看看this related questionthis answer(由我) .

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

  • 1

    它们不是同一件事 .

    考虑这些查询:

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

  • 0

    这是我的解决方案 .

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

    希望这有帮助 .

  • 8

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

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

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

  • 0

    对于内连接, 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 链接到任何东西(因为"driver"表不合法 .

  • 698
    • 对于内连接无关紧要

    • 外连接的事项

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

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

    Example :请考虑以下表格:

    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 |
    
  • 1

    当涉及左连接时, where clauseon clause 之间有很大差异 .

    这是一个例子:

    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)
    

    Query on "on clause" :

    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)
    

    Query on "where clause":

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

  • 7

    我这样做的方式是:

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

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

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

  • 1

    This article清楚地解释了差异 . 它还解释了"ON joined_condition vs WHERE joined_condition or joined_alias is null" .

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

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

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

  • 37

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

相关问题