SQL語句中LEFT JOIN的ON和WHERE有什么區(qū)別
闡述
寫SQL時本想通過 A left B join on and
后面的條件查出的兩條記錄變成一條,奈何發(fā)現(xiàn)還是有兩條。
后來發(fā)現(xiàn) join on and
不會過濾結(jié)果記錄條數(shù),只會根據(jù) and 后的條件是否顯示 B
表的記錄,A
表的記錄一定會顯示。
不管 and 后面的是 A.id=1
還是 B.id=1
,都顯示出 A
表中所有的記錄,并關(guān)聯(lián)顯示 B
中對應(yīng) 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ìn)行過濾的條件。這時已經(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)
其實以上結(jié)果的關(guān)鍵原因就是 left join,right join,full join
的特殊性,不管 on 上的條件是否為真都會返回 left 或 right 表中的記錄。
full 則具有 left 和 right 的特性的并集。
而 inner join 沒這個特殊性,則條件放在 on 中和 where 中,返回的結(jié)果集是相同的。
到此這篇關(guān)于SQL語句中LEFT JOIN的ON和WHERE有什么區(qū)別的文章就介紹到這了,更多相關(guān)SQL LEFT JOIN內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
很全面的Mysql數(shù)據(jù)庫、數(shù)據(jù)庫表、數(shù)據(jù)基礎(chǔ)操作筆記(含代碼)
這篇文章主要為大家分享了很全面的Mysql數(shù)據(jù)庫、數(shù)據(jù)庫表、數(shù)據(jù)基礎(chǔ)操作筆記,感興趣的小伙伴們可以參考一下2016-04-04一臺服務(wù)器部署兩個獨立的mysql數(shù)據(jù)庫操作實例
這篇文章主要給大家介紹了關(guān)于一臺服務(wù)器部署兩個獨立的mysql數(shù)據(jù)庫的相關(guān)資料,同一臺服務(wù)器裝兩個數(shù)據(jù)庫,可以通過虛擬化技術(shù)實現(xiàn),文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-03-03mysql5.7大量sleep進(jìn)程常規(guī)處理方式及配置示例
這篇文章主要給大家介紹了關(guān)于mysql5.7大量sleep進(jìn)程常規(guī)處理方式及配置的相關(guān)資料,sleep連接過多會嚴(yán)重消耗mysql服務(wù)器資源(主要是cpu,內(nèi)存),并可能導(dǎo)致mysql崩潰,需要的朋友可以參考下2023-08-08MySQL OOM 系統(tǒng)二 OOM Killer
前面一節(jié)重點分享了Linux的內(nèi)存分配策略,基于上述的分配策略,為了規(guī)避超售的風(fēng)險,Linux采了一種OOM Killer的機(jī)制,即系統(tǒng)可用內(nèi)存(包括Swap)即將使用完之前,選擇性的Kill掉一些進(jìn)程以求釋放一些內(nèi)存2016-07-07mysql數(shù)據(jù)庫replace、regexp的用法
mysql數(shù)據(jù)庫中replace、regexp的用法,主要是通過sql語句實現(xiàn)數(shù)據(jù)的替換,使用mysql的朋友可以參考下。2011-08-08數(shù)據(jù)庫設(shè)計工具M(jìn)ySQL?Workbench使用教程(超級詳細(xì)!)
MySQL?Workbench為數(shù)據(jù)庫管理員、程序開發(fā)者和系統(tǒng)規(guī)劃師提供可視化的Sql開發(fā)、數(shù)據(jù)庫建模、以及數(shù)據(jù)庫管理功能,下面這篇文章主要給大家介紹了關(guān)于MySQL設(shè)計工具Workbench使用的相關(guān)資料,需要的朋友可以參考下2023-02-02開發(fā)人員為什么必須要了解數(shù)據(jù)庫鎖詳解
這篇文章主要給大家介紹了關(guān)于開發(fā)人員為什么必須要了解數(shù)據(jù)庫鎖的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家學(xué)習(xí)或者使用數(shù)據(jù)庫具有一定的參考學(xué)習(xí)價值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-03-03