sql同時查詢兩個資料表 - sql語法where\




SQL連接:where子句與on子句 (10)

閱讀後,這不是 顯式與隱式SQL聯接的重複。 答案可能相關(甚至相同),但問題不同。

各有什麼區別和應該怎麼做?

如果我理解正確,那麼查詢優化器應該可以互換使用。


這篇文章清楚地解釋了差異。 它還解釋了“ON joined_condition vs WHERE joined_condition或joined_alias為null”。

WHERE子句過濾JOIN的左側和右側,而ON子句只會過濾右側。

  1. 如果你總是想獲取左側行,並且只有在某些條件匹配時才加入,那麼你應該使用ON子句。
  2. 如果要過濾連接雙方的產品,則應使用WHERE子句。

INNER JOIN它們是可以互換的,優化器會隨意重新排列它們。

OUTER JOIN ,它們不一定是可以互換的,這取決於它們所依賴的連接的哪一側。

我根據可讀性將它們放在任何一個地方。


在內部聯接上,他們的意思是相同的。 但是,根據將聯接條件放在WHERE和ON子句中,您將在外部聯接中獲得不同的結果。 看看這個相關的問題這個答案 (由我)。

我認為最有意義的是始終將連接條件放在ON子句中(除非它是外部連接,並且實際上確實希望它在where子句中),因為它使讀取查詢的任何人都更清楚這些表正在被連接到什麼條件,還有助於防止WHERE子句長達數十行。


它們不是同一件事。

考慮這些查詢:

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 ,這些條款實際上是等效的。 然而,僅僅因為它們在功能上是相同的,因為它們產生相同的結果,並不意味著這兩種子句具有相同的語義含義。


就優化器而言,無論您是使用ON還是WHERE定義連接子句,都沒有什麼區別。

但是,恕我直言,我認為在執行聯接時使用ON子句會更清晰。 這樣,你有一個特定的查詢部分,它決定瞭如何處理連接,以及如何與WHERE子句的其餘部分混合處理。


我認為這是連接順序效應。 在左上角連接的情況下,SQL先左連接,然後執行篩選。 在downer案例中,首先找到Orders.ID = 12345,然後加入。


為了獲得更好的性能,表格應該有一個專門用於JOINS的索引列。

所以如果你的條件列不是那些索引列之一,那麼我懷疑最好把它放在WHERE。

所以你JOIN使用索引列,然後JOIN後,您在沒有索引的列上運行條件。


當涉及到左連接時, 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 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)

查詢“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返回一條記錄,並從t2返回從屬行,如果有的話,對於行t1.v ='K'。

第二個查詢返回來自t1的行,但只有對於t1.v ='K'才會有任何關聯的行。


通常情況下,一旦兩個表已經連接,就會在WHERE子句中處理過濾。 這是可能的,儘管你可能想要在加入它們之前過濾一個或兩個表格。 即where子句適用於整個結果集,而on子句僅適用於有問題的連接。


  • 內連接無關緊要
  • 外連接的問題

    一個。 WHERE子句:加入後。 記錄將在聯接發生後被過濾。

    ON條款 - 加入 。 記錄(來自右表)將在加入前被過濾。 這可能會以結果為空(因為外部連接)。



示例 :考慮下表:

    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 | 




on-clause