mysql的left join使用on與where篩選的差異

標籤：prim   pre   資料庫   group   from   engine   情境   舉例   沒有   

    舉例進行說明，我們現在有兩個表，即商品表(products)與sales_detail(銷售記錄表)。我們主要是通過這兩個表來對MySQL關聯left join 條件on與where 條件的不同之處進行講述。

 1、資料準備

建立products表並插入資料

drop table if exists products;   CREATE TABLE `products` (    `pid` INT (3) NOT NULL auto_increment,    `pname` VARCHAR (20) NOT NULL,    `pcode` VARCHAR (20) NOT NULL,    PRIMARY KEY (`pid`)) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;INSERT INTO `products` (`pid`, `pname`, `pcode`)VALUES    (1, ‘商品1‘, ‘AC90‘),    (2, ‘商品2‘, ‘DE78‘),    (3, ‘商品3‘, ‘XXXX‘);

 建立sales_detail表並插入資料

drop table if exists sales_detail;CREATE TABLE `sales_detail` (    `aid` INT (3) NOT NULL auto_increment,    `pcode` VARCHAR (20) NOT NULL,    `saletime` date NOT NULL,    PRIMARY KEY (`aid`)) ENGINE = MyISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1;INSERT INTO `sales_detail` (`aid`, `pcode`, `saletime`)VALUES    (1, ‘AC90‘, ‘2008-09-22‘),    (2, ‘DE78‘, ‘2008-09-22‘),    (3, ‘AC90‘, ‘2008-09-23‘),    (4, ‘AC90‘, ‘2008-09-24‘);

 

 資料庫中的資料如下：

products表

pid	pname	pcode
1	商品1	AC90
2	商品2	DE78
3	商品3	XXXX

sales_detail表

aid	pcode	saletime
1	AC90	2008-09-22
2	DE78	2008-09-22
3	AC90	2008-09-23
4	AC90	2008-09-24

2、測試

現在有個情境，按商品在某個時間段內的銷售量來排行，比如我想統計23－24號這兩天的銷售數量並排行。（註：DE78這個商品在這兩天沒有銷售，但是也要顯示出來，只是數量為0）

使用where條件查詢：

SELECT    p.pname,    p.pcode,    s.saletime,    count(s.aid) AS totalFROM    products AS pLEFT JOIN sales_detail AS s ON (s.pcode = p.pcode)WHERE    s.saletime IN (‘2008-09-23‘, ‘2008-09-24‘)GROUP BY    p.pcodeORDER BY    total DESC,    p.pid ASC

結果：

pname	pcode	saletime	total
商品1	AC90	2008-09-23	2

 

 

這裡的查詢過程可以分成兩部，首先通過on條件產生中間表（總共有3條資料），然後用where條件過濾中間表得到最後的結果。

使用on條件查詢：

SELECT    p.pname,    p.pcode,    s.saletime,    count(s.aid) AS totalFROM    products AS pLEFT JOIN sales_detail AS s ON (    (s.pcode = p.pcode)    AND s.saletime IN (‘2008-09-23‘, ‘2008-09-24‘))GROUP BY    p.pcodeORDER BY    total DESC,    p.pid ASC

結果：

pname	pcode	saletime	total
商品1	AC90	2009-09-23	2
商品2	DE78	NULL	0
商品3	XXXX	NULL	0

 

 

 

 

這裡直接通過on條件得到結果，不管on上的條件是否為真都會返回left表中的記錄，如果需要不滿足串連條件的行也出現在查詢結果中，必須把串連條件放在on上。以上查詢等價於：

SELECT    p.pname,    p.pcode,    s.saletime,    count(s.aid) AS totalFROM    products AS pLEFT JOIN  (select * from sales_detail s where s.saletime IN (‘2008-09-23‘, ‘2008-09-24‘)) as s ON     (s.pcode = p.pcode)GROUP BY    p.pcodeORDER BY    total DESC,    p.pid ASC

 

 3、結論

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

  在使用left jion時，on和where條件的區別如下：

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

2、where條件是在暫存資料表產生好後，再對暫存資料表進行過濾的條件。這時已經沒有left join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。

mysql的left join使用on與where篩選的差異