解析mysql left( right ) join使用on與where篩選的差異
更新時間：2013年06月18日 10:36:01 作者：
本篇文章是對mysql left( right )join使用on與where篩選的差異進行了詳細的分析介紹，需要的朋友參考下
有這樣的一個問題mysql查詢使用mysql中l(wèi)eft(right)join篩選條件在on與where查詢出的數據是否有差異。
可能只看著兩個關鍵字看不出任何的問題。那我們使用實際的例子來說到底有沒有差異。
例如存在兩張表結構
表結構1
drop table if EXISTS A;
CREATE TABLE A (
ID int(1) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
表結構2
drop table if EXISTS B;
CREATE TABLE B (
ID int(1) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
表一插入數據
insert into A values ( 1 );
insert into A values ( 2 );
insert into A values ( 3 );
insert into A values ( 4 );
insert into A values ( 5 );
insert into A values ( 6 );
表二插入數據
insert into B values ( 1 );
insert into B values ( 2 );
insert into B values ( 3 );
完成后A,B表數據如下：
select A.ID as AID, B.ID as BID from A left join B on A.ID = B.ID where B.ID<3
語句二
select A.ID as AID, B.ID as BID from A left join B on A.ID = B.ID and B.ID<3
以上兩個語句的查詢結果是否一致。
反正一切我是沒有注意到這兩個查詢存在任何差異的【以前也沒這么寫過sql】。
我們看看實際結果
語句一的查詢結果
select A.ID as AID, B.ID as BID from A left join B on A.ID = B.ID
查詢結果如下
select A.ID as AID, B.ID as BID from A left join B on A.ID = B.ID and A.ID = 3
這個的查詢結果為
select A.ID as AID, B1.ID as BID
from A left join ( select B.ID from B where B.ID <3 )B1 on A.ID = B1.ID
以上全在mysql5.1上測試過
可能只看著兩個關鍵字看不出任何的問題。那我們使用實際的例子來說到底有沒有差異。
例如存在兩張表結構
表結構1
復制代碼 代碼如下:
drop table if EXISTS A;
CREATE TABLE A (
ID int(1) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
表結構2
復制代碼 代碼如下:
drop table if EXISTS B;
CREATE TABLE B (
ID int(1) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
表一插入數據
復制代碼 代碼如下:
insert into A values ( 1 );
insert into A values ( 2 );
insert into A values ( 3 );
insert into A values ( 4 );
insert into A values ( 5 );
insert into A values ( 6 );
表二插入數據
復制代碼 代碼如下:
insert into B values ( 1 );
insert into B values ( 2 );
insert into B values ( 3 );
完成后A,B表數據如下：
語句一
復制代碼 代碼如下:
select A.ID as AID, B.ID as BID from A left join B on A.ID = B.ID where B.ID<3
語句二
復制代碼 代碼如下:
select A.ID as AID, B.ID as BID from A left join B on A.ID = B.ID and B.ID<3
以上兩個語句的查詢結果是否一致。
反正一切我是沒有注意到這兩個查詢存在任何差異的【以前也沒這么寫過sql】。
我們看看實際結果
語句一的查詢結果
語句二的查詢結果為：
發(fā)現兩個查詢存在差異。
為什么會存在差異，這和on與where查詢順序有關。
我們知道標準查詢關鍵字執(zhí)行順序為 from->where->group by->having->order by[ 記得不是很清楚呢]
left join 是在from范圍類所以 先on條件篩選表，然后兩表再做left join。
而對于where來說在left join結果再次篩選。
第一sql語句查詢過程如下等價于：
1:先是left join
復制代碼 代碼如下:
select A.ID as AID, B.ID as BID from A left join B on A.ID = B.ID
查詢結果如下
2：再查詢結果中將B.ID即BID<2篩選出來。
也就是我們上面看到的結果。
第二sql語句查詢過程如下等價于：
1：先按照on條件刷選表等價于先篩選B表：
2：再已上查詢結果與A表做left join，這也是為什么我們看到第二個查詢的sql會保留A表的原因。
ON與where的使用一定要注意場所：
（1）：ON后面的篩選條件主要是針對的是關聯表【而對于主表刷選條件不適用】。
例如
復制代碼 代碼如下:
select A.ID as AID, B.ID as BID from A left join B on A.ID = B.ID and A.ID = 3
這個的查詢結果為
挺詫異的吧和我們期望的結果不一樣，并為篩選出AID=3的數據。
但是我們也發(fā)現 AID 與 中AID 1 于2對應的值為NULL，關聯表只取了滿足A表篩刷選條件的值。
即主表條件在on后面時附表只取滿足主表帥選條件的值、而主表還是取整表。
（2）：對于主表的篩選條件應放在where后面，不應該放在ON后面
（3）：對于關聯表我們要區(qū)分對待。如果是要條件查詢后才連接應該把查詢件
放置于ON后。
如果是想再連接完畢后才篩選就應把條件放置于where后面
（4）： 對于關聯表我們其實可以先做子查詢再做join
所以第二個sql等價于
復制代碼 代碼如下:
select A.ID as AID, B1.ID as BID
from A left join ( select B.ID from B where B.ID <3 )B1 on A.ID = B1.ID
以上全在mysql5.1上測試過
相關文章
在本篇文章里小編給大家整理了關于php和html的區(qū)別點，有需要的朋友們可以參考下。2019-09-09
在網站的開發(fā)過程中，常常需要對部分數據（如用戶密碼）進行加密，本文主要介紹PHP的幾個常見的加密函數,需要的朋友可以參考下2016-02-02
這篇文章主要給大家介紹了關于PHP中static和yield關鍵字的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用php具有一定的參考學習價值，文章需要的朋友們下面隨著小編來一起學習學習吧。2017-09-09
在本篇文章中，我們給大家?guī)砹岁P于php刪除鏈表中重復的結點的相關知識點內容以及相關代碼，有興趣的朋友們參考下。2018-09-09
php5字符串處理函數小結，對于使用php的朋友經常用得到的一些函數。方便查找使用。2010-03-03
這篇文章主要介紹了PHP函數func_num_args用法,結合實例形式較為詳細的分析了func_num_args函數返回函數參數的相關使用技巧,具有一定參考借鑒價值,需要的朋友可以參考下2015-12-12
本文主要介紹，解決PHP微信開發(fā)時數據緩存的問題,這里用Cache 類舉例說明,具有參考價值,感興趣的小伙伴可以參考下2016-07-07
這篇文章主要介紹了PHP標準庫 (SPL)——Countable用法,結合實例形式分析了PHP標準庫 (SPL)Countable基本功能、原理、用法及操作注意事項,需要的朋友可以參考下2020-06-06
本篇文章主要是對php初始化對象和析構函數的簡單實例進行了介紹，需要的朋友可以過來參考下，希望對大家有所幫助2014-03-03
本篇文章給大家分享了PHP繞過open_basedir限制操作文件的三種方法以及相關技巧，有興趣的朋友參考學習下。2018-06-06