SQL語句中LEFT JOIN的ON和WHERE有什麼區別

2022-12-19 14:01:52

闡述

寫SQL時本想通過 A left B join on and 後面的條件查出的兩條記錄變成一條，奈何發現還是有兩條。

後來發現 join on and 不會過濾結果記錄條數，只會根據 and 後的條件是否顯示 B 表的記錄，A 表的記錄一定會顯示。

不管 and 後面的是 A.id=1 還是 B.id=1,都顯示出 A 表中所有的記錄，並關聯顯示 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

資料庫在通過連線兩張或多張表來返回記錄時，都會生成一張中間的臨時表，然後再將這張臨時表返回給使用者。

left join的on和where的條件區別

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

2、where 條件是在臨時表生成好後，再對臨時表進行過濾的條件。這時已經沒有 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有什麼區別的文章就介紹到這了,更多相關SQL LEFT JOIN內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

SQL語句中LEFT JOIN的ON和WHERE有什麼區別

目錄

闡述

left join的on和where的條件區別

第一條SQL的過程

第二條SQL的過程

熱門文章