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

    舉例進行說明，我們現在有兩個表，即商品表(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）

使用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

結果：

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

使用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

結果：

這裡直接通過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

 3、結論

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

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

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

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