SQL夯實基礎（二）：連接配接操作中使用on與where篩選的差異

SQL夯實基礎（二）：連接配接操作中使用on與where篩選的差異

一、on篩選和where篩選

　　在連接配接查詢文法中，另人迷惑首當其沖的就要屬on篩選和where篩選的差別了，如果在我們編寫查詢的時候， 篩選條件的放置不管是在on後面還是where後面， 查出來的結果總是一樣的， 既然如此，那為什麼還要多此一舉的讓sql查詢支援兩種篩選器呢？  事實上， 這兩種篩選器是存在差别的，隻是如果不深挖不容易發現而已。（on是能消除笛卡爾積的過濾條件，而where是對已經關聯之後的集合進行篩選。）

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

舉例進行說明（案例使用mysql），我們現在有兩個表，即商品表(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');      

2、測試

現在有個場景，按商品在某個時間段内的銷售量來排行，比如我想統計23－24号這兩天的銷售數量并排行。（注：DE78這個商品在這兩天沒有銷售，但是也要顯示出來，隻是數量為0）

　　2.1 使用where條件查詢：

SELECT
p.pname,
p.pcode,
s.saletime,
count(s.aid) AS total
FROM
products AS p
LEFT JOIN sales_detail AS s ON (s.pcode = p.pcode)
WHERE
s.saletime IN ('2008-09-23', '2008-09-24')
GROUP BY
p.pcode
ORDER BY
total DESC,
p.pid ASC      

結果：

pname pcode  saletime   total

商品1   AC90  2008-09-23  2      

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

　　2.2 查詢過程說明

查詢中用到的關鍵詞主要包含六個，并且他們的順序依次為

select--from--where--group by--having--order by

其中select和from是必須的，其他關鍵詞是可選的，這六個關鍵詞的執行順序與sql語句的書寫順序并不是一樣的，而是按照下面的順序來執行

from--where--group by--having--select--order by

from:需要從哪個資料表檢索資料

where:過濾表中資料的條件

group by:如何将上面過濾出的資料分組

having:對上面已經分組的資料進行過濾的條件

select:檢視結果集中的哪個列，或列的計算結果

order by :按照什麼樣的順序來檢視傳回的資料

　　其中from後面的表關聯，是自右向左解析的。也就是說，在寫SQL文的時候，盡量把資料量小的表放在最右邊來進行關聯（用小表去比對大表），而把能篩選出小量資料的條件放在where語句的最左邊 （用小表去比對大表）。（這裡也不完全正确，mysql會對這種關系進行優化，這裡想表述的就是盡量使用小表做驅動表）

　　2.3 使用on條件查詢：

SELECT
p.pname,
p.pcode,
s.saletime,
count(s.aid) AS total
FROM
products AS p
LEFT JOIN sales_detail AS s ON (
(s.pcode = p.pcode)
AND s.saletime IN ('2008-09-23', '2008-09-24')
)
GROUP BY
p.pcode
ORDER 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 total
FROM
products AS p
LEFT 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.pcode
ORDER BY
total DESC,
p.pid ASC      

三、結論

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

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

1、 on條件是在生成臨時表時使用的條件，他會去篩選右邊關聯的表。

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