SQL語句中LEFT JOIN的ON和WHERE有什么區(qū)別

更新時間：2022年12月16日 14:31:20 作者：知其黑、受其白

這篇文章主要介紹了SQL語句中LEFT JOIN的ON和WHERE之間的區(qū)別，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習吧

闡述

寫SQL時本想通過 A left B join on and 后面的條件查出的兩條記錄變成一條，奈何發(fā)現(xiàn)還是有兩條。

后來發(fā)現(xiàn) join on and 不會過濾結果記錄條數(shù)，只會根據(jù) and 后的條件是否顯示 B 表的記錄，A 表的記錄一定會顯示。

不管 and 后面的是 A.id=1 還是 B.id=1,都顯示出 A 表中所有的記錄，并關聯(lián)顯示 B 中對應 A 表中 id 為 1 的記錄或者B表中 id 為 1 的記錄。

SELECT u.id,u.nickname,c.content FROM chat_user u
LEFT JOIN chat_communication c
on u.id=c.toid order by c.id

+----+------------------+--------------+
| id | nickname | content |
+----+------------------+--------------+
| 86 | 大美如斯 | NULL |
| 88 | 悅悅 | NULL |
| 89 | 雨薇 | NULL |
| 87 | 大金 | 你好 |
| 85 | Love violet life | 你也好 |
| 87 | 大金 | 你在干嘛 |
| 87 | 大金 | 你還在嗎 |
| 87 | 大金 | 訂單 |
+----+------------------+--------------+
8 rows in set (0.00 sec)

SELECT u.id,u.nickname,c.content FROM chat_user u
LEFT JOIN chat_communication c
on u.id=c.toid and u.nickname="大金" order by c.id
+----+------------------+--------------+
| id | nickname         | content      |
+----+------------------+--------------+
| 85 | Love violet life | NULL         |
| 86 | 大美如斯         | NULL         |
| 88 | 悅悅             | NULL         |
| 89 | 雨薇             | NULL         |
| 87 | 大金             | 你好         |
| 87 | 大金             | 你在干嘛     |
| 87 | 大金             | 你還在嗎     |
| 87 | 大金             | 訂單         |
+----+------------------+--------------+
8 rows in set (0.00 sec)

SELECT u.id,u.nickname,c.content,c.fromname FROM chat_user u
LEFT JOIN chat_communication c
on u.id=c.toid and c.fromname="雨薇" order by c.id

數(shù)據(jù)庫在通過連接兩張或多張表來返回記錄時，都會生成一張中間的臨時表，然后再將這張臨時表返回給用戶。

left join的on和where的條件區(qū)別

1、 on 條件是在生成臨時表時使用的條件，它不管 on 中的條件是否為真，都會返回左邊表中的記錄。

2、where 條件是在臨時表生成好后，再對臨時表進行過濾的條件。這時已經(jīng)沒有 left join 的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。

新建兩張表：

CREATE TABLE test1(id int,size int); 
INSERT INTO test1 VALUES (1,10),(2,20),(3,30); 
CREATE TABLE test2(size int,nikname varchar(10));
INSERT INTO test2 VALUES (10,"AAA"),(20,"BBB"),(20,"CCC");

表1：test1

mysql> select * from test1;
+------+------+
| id   | size |
+------+------+
|    1 |   10 |
|    2 |   20 |
|    3 |   30 |
+------+------+
3 rows in set (0.00 sec)

表2：test2

+------+---------+
| size | nikname |
+------+---------+
|   10 | AAA     |
|   20 | BBB     |
|   20 | CCC     |
+------+---------+
3 rows in set (0.01 sec)

第一條 SQL

SELECT * FROM test1
LEFT JOIN test2 ON (test1.size = test2.size)
WHERE test2.nikname = "AAA";
+------+------+------+---------+
| id   | size | size | nikname |
+------+------+------+---------+
|    1 |   10 |   10 | AAA     |
+------+------+------+---------+
1 row in set (0.00 sec)

第二條 SQL

select * FROM test1 left join test2 
on (test1.size = test2.size and test2.nikname="AAA");
+------+------+------+---------+
| id   | size | size | nikname |
+------+------+------+---------+
|    1 |   10 |   10 | AAA     |
|    2 |   20 | NULL | NULL    |
|    3 |   30 | NULL | NULL    |
+------+------+------+---------+
3 rows in set (0.00 sec)

第一條SQL的過程

1、中間表 on 條件 : test1.size = test2.size

+------+------+------+---------+
| id   | size | size | nikname |
+------+------+------+---------+
|    1 |   10 |   10 | AAA     |
|    2 |   20 |   20 | BBB     |
|    2 |   20 |   20 | CCC     |
|    3 |   30 | NULL | NULL    |
+------+------+------+---------+
4 rows in set (0.00 sec)

2、再對中間表過濾 where 條件：test2.nikname="AAA"

+------+------+------+---------+
| id   | size | size | nikname |
+------+------+------+---------+
|    1 |   10 |   10 | AAA     |
+------+------+------+---------+
1 row in set (0.00 sec)

第二條SQL的過程

1、中間表on條件:test1.size = test2.size and test2.nikname=’AAA’

(條件不為真也會返回左表中的記錄)

+------+------+------+---------+
| id   | size | size | nikname |
+------+------+------+---------+
|    1 |   10 |   10 | AAA     |
|    2 |   20 | NULL | NULL    |
|    3 |   30 | NULL | NULL    |
+------+------+------+---------+
3 rows in set (0.00 sec)

其實以上結果的關鍵原因就是 left join,right join,full join 的特殊性，不管 on 上的條件是否為真都會返回 left 或 right 表中的記錄。

full 則具有 left 和 right 的特性的并集。

而 inner join 沒這個特殊性，則條件放在 on 中和 where 中，返回的結果集是相同的。

到此這篇關于SQL語句中LEFT JOIN的ON和WHERE有什么區(qū)別的文章就介紹到這了,更多相關SQL LEFT JOIN內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: