1、三種連接的語法
為便於更多的技友快速讀懂、理解，我們只討論2張表對象進行連接操作的情況，大於2張表對象進行的連接操作原理也是一樣的。
左連接(LEFT JOIN )
SELECT M.columnname……,N.* columnname…..FROM left_table M LEFT JOIN right_table N ON M. columnname_join=N. columnname_join AND N. columnname=XXX WHERE M.columnname=XXX…..
右連接(RIGHT JOIN)
SELECT M.columnname……,N.* columnname…..FROM left_table M RIGHT JOIN right_table N ON M. columnname_join=N. columnname_join AND M. columnname=XXXWHERE N.columnname=XXX…..
等值連接
SELECT M.columnname……,N.* columnname…..FROM left_table M [INNER] JOIN right_table N ON M. columnname_join=N. columnname_joinWHERE M.columnname=XXX….. AND N.columnname=XXX….或者SELECT M.columnname……,N.* columnname…..FROM left_table M , right_table NWHERE M. columnname_join=N. columnname_join AND M.columnname=XXX….. AND N.columnname=XXX….
備注：注意上面SQL語法中加粗的紅色字體部分,三種不同連接語法的變化。
三種連接的特性
左連接(LEFT JOIN )
ON字句連接條件，用於把2表中等值的記錄連接在一起，但是不影響記錄集的數量。若是表left_table中的某記錄，無法在表right_table找到對應的記錄，則此記錄依然顯示在記錄集鐘，只表 right_table需要在查詢顯示的列的值用NULL替代；
ON字句連接條件中表right_table. columnname=XXX用於控制right_table表是否有符合要求的列值還是用NULL替換的方式顯示在查詢列中，不影響記錄集的數量；
WHERE字句控制記錄是否符合查詢要求，不符合則過濾掉；
總結：ON字句控制right_table的列值符合顯示，還是不符合就用NULL替換，不影響最終符合查詢要求的記錄集;WHERE字句是控制那些記錄是顯示在最終的記錄集中。
右連接(RIGHT JOIN)
ON字句連接條件，用於把2表中等值的記錄連接在一起，若是表right_table中的某記錄，無法在表left_table找到對應的記錄，則表 left_able需要在查詢顯示的列的值用NULL替代；
ON字句連接條件中表left_table. columnname=XXX用於控制left_table表是否有符合要求的列值，還是用NULL替換的方式顯示在查詢列表中；
WHERE字句控制記錄是否符合查詢要求，不符合則過濾掉；
總結：ON字句控制left_table的列值符合顯示，還是不符合而用NULL替換掉，不影響最終符合查詢要求的記錄集;WHERE字句是控制那些記錄是顯示在最終的記錄集中。我們會發現LEFT JOIN 和RIGHT JOIN是類似的，只是以連接關鍵字左邊還是右邊表為准匹配。
等值連接
ON 字句連接條件，不再與左連接或右連接的功效一樣，除了作為2表記錄匹配的條件外，還會起到過濾記錄的作用，若left_table中記錄無法在right_table中找到對應的記錄，則會被過濾掉；
WHERE字句，不管是涉及表left_table、表right_table上的限制條件，還是涉及2表連接的條件，都會對記錄集起到過濾作用，把不符合要求的記錄刷選掉；
連接語法的測試環境
測試用表結構
CREATE TABLE left_table(ID INT UNSIGNED NOT NULL AUTO_INCREMENT, Username VARCHAR(40) NOT NULL, Birthday DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', CityID SMALLINT NOT NULL DEFAULT 0, CreatDate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', AlterDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(ID), KEY idx_username(Username) )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';CREATE TABLE right_table(UID INT UNSIGNED NOT NULL , CollectNum MEDIUMINT NOT NULL DEFAULT 0, BuyNum MEDIUMINT NOT NULL DEFAULT 0, SearchNum MEDIUMINT NOT NULL DEFAULT 0, CreatDate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', AlterDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(UID) )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
用於測試的數據
重復執行10次INSERT*** VALUE ***語句：
INSERT INTO left_table(Username,Birthday,CityID,CreatDate,AlterDate)VALUES(CONCAT(SUBSTRING(RAND(),3,8),'@QQ.com'),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) YEAR),SUBSTRING(RAND(),3,2),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,3) DAY),DATE_ADD(NOW(),INTERVAL -SUBSTRING(RAND(),3,2) DAY));
執行一次INSERT ** SELECT **語句：
INSERT INTO right_table SELECT ID,SUBSTRING(RAND(),3,4) AS CollectNum,SUBSTRING(RAND(),3,2) AS BuyNum,SUBSTRING(RAND(),3,3) AS SearchNum,CreatDate,AlterDateFROM left_table WHERE ID%5=1;
注釋：表left_table將會有ID值1,2,3,***,10連續的記錄10條，表right_table中有ID值1,6離散的記錄2條，並且2表關聯條件為：left_table.ID=right_table.UID實現。
對比測試
基准測試表中將可能看到的數據(編號：SQL_1)
root@localhost : eugene 03:25:07> SELECT M.ID,M.username FROM left_table M WHERE M.ID<=6;+----+-----------------+| ID | username |+----+-----------------+| 1 | [email protected] || 2 | [email protected] || 3 | [email protected] || 4 | [email protected] || 5 | [email protected] || 6 | [email protected] | +----+-----------------+
標准左連接
ON字句中無連接字段之外條件的SQL及數據(編號：SQL_2)
root@localhost : eugene 03:37:58> SELECT M.ID,M.username,N.CollectNum,N.BuyNum-> FROM left_table M LEFT JOIN right_table N ON M.ID=N.UID -> WHERE M.ID<=6;+----+-----------------+------------+--------+| ID | username | CollectNum | BuyNum |+----+-----------------+------------+--------+| 1 | [email protected] | 817 | 39 || 2 | [email protected] | NULL | NULL || 3 | [email protected] | NULL | NULL || 4 | [email protected] | NULL | NULL || 5 | [email protected] | NULL | NULL || 6 | [email protected] | 5177 | 36 | +----+-----------------+------------+--------+
ON字句中除連接字段條件之外，還有right_table限制條件的SQL及數據(編號：SQL_3)
root@localhost : eugene 03:40:20> SELECT M.ID,M.username,N.CollectNum,N.BuyNum-> FROM left_table M LEFT JOIN right_table N ON M.ID=N.UID AND N.SearchNum>300 -> WHERE M.ID<=6;+----+-----------------+------------+--------+| ID | username | CollectNum | BuyNum |+----+-----------------+------------+--------+| 1 | [email protected] | 817 | 39 || 2 | [email protected] | NULL | NULL || 3 | [email protected] | NULL | NULL || 4 | [email protected] | NULL | NULL || 5 | [email protected] | NULL | NULL || 6 | [email protected] | NULL | NULL | +----+-----------------+------------+--------+
帶JOIN關鍵字的等值連接
對應編號：SQL_2的等值連接測試的SQL及數據(編號：SQL_4)
root@localhost : eugene 03:41:27> SELECT M.ID,M.username,N.CollectNum,N.BuyNum-> FROM left_table M INNER JOIN right_table N ON M.ID=N.UID -> WHERE M.ID<=6;+----+-----------------+------------+--------+| ID | username | CollectNum | BuyNum |+----+-----------------+------------+--------+| 1 | [email protected] | 817 | 39 || 6 | [email protected] | 5177 | 36 | +----+-----------------+------------+--------+
對應編號：SQL_3的等值連接測試的SQL及數據(編號：SQL_5)
root@localhost : eugene 03:42:53> SELECT M.ID,M.username,N.CollectNum,N.BuyNum-> FROM left_table M INNER JOIN right_table N ON M.ID=N.UID AND N.SearchNum>300-> WHERE M.ID<=6;+----+-----------------+------------+--------+| ID | username | CollectNum | BuyNum |+----+-----------------+------------+--------+| 1 | [email protected] | 817 | 39 | +----+-----------------+------------+--------+
不帶JOIN關鍵字的等值連接
對應編號：SQL_4的等值連接測試的SQL及數據(編號：SQL_6)
root@localhost : eugene 03:43:01> SELECT M.ID,M.username,N.CollectNum,N.BuyNum-> FROM left_table M,right_table N-> WHERE M.ID=N.UID AND M.ID<=6;+----+-----------------+------------+--------+| ID | username | CollectNum | BuyNum |+----+-----------------+------------+--------+| 1 | [email protected] | 817 | 39 || 6 | [email protected] | 5177 | 36 | +----+-----------------+------------+--------+
對應編號：SQL_5的等值連接測試的SQL及數據(編號：SQL_7)
root@localhost : eugene 03:49:35> SELECT M.ID,M.username,N.CollectNum,N.BuyNum-> FROM left_table M,right_table N -> WHERE M.ID=N.UID AND M.ID<=6 AND N.SearchNum>300;+----+-----------------+------------+--------+| ID | username | CollectNum | BuyNum |+----+-----------------+------------+--------+| 1 | [email protected] | 817 | 39 | +----+-----------------+------------+--------+
常見錯誤用法的左連接
錯把限制表right_table的條件，從ON 子句中放到WHERE字句中的SQL及數據(編號：SQL_8)
root@localhost : eugene 03:49:57> SELECT M.ID,M.username,N.CollectNum,N.BuyNum-> FROM left_table M LEFT JOIN right_table N ON M.ID=N.UID -> WHERE M.ID<=6 AND N.SearchNum>300;+----+-----------------+------------+--------+| ID | username | CollectNum | BuyNum |+----+-----------------+------------+--------+| 1 | [email protected] | 817 | 39 | +----+-----------------+------------+--------+
錯把限制表left_table或稱影響最終記錄集的條件，從WHERE子句中放到ON字句中的SQL及數據(編號：SQL_9)
root@localhost : eugene 03:54:14> SELECT M.ID,M.username,N.CollectNum,N.BuyNum-> FROM left_table M LEFT JOIN right_table N ON M.ID=N.UID AND M.ID<=6 AND N.SearchNum>300;+----+-----------------+------------+--------+| ID | username | CollectNum | BuyNum |+----+-----------------+------------+--------+| 6 | [email protected] | NULL | NULL || 7 | [email protected] | NULL | NULL || 9 | [email protected] | NULL | NULL || 1 | [email protected] | 817 | 39 || 4 | [email protected] | NULL | NULL || 2 | [email protected] | NULL | NULL || 10 | [email protected] | NULL | NULL || 3 | [email protected] | NULL | NULL || 5 | [email protected] | NULL | NULL || 8 | [email protected] | NULL | NULL | +----+-----------------+------------+--------+
測試總結
編號SQL_1：表left_table真實參與測試的記錄的ID值：1,2,3,4,5,6共計6條連續的記錄；
編號SQL_2、SQL_3：對於LEFT JOIN 連接操作，增加right_table的限制條件，不影響最後記錄集的數量，但是影響right_table表中需要顯示出來的列值，不符合限制條件的情況下，則用NULL值替代；
編號SQL_4、SQL_5：對於等值連接操作，ON字句中增加對right_table的限制條件，會對最終的記錄集數量產生影響；
編號SQL_6、SQL_7：對於等值連接操作，WHERE字句中增加對right_table的限制條件，會對最終的記錄集數量產生影響；
編號(SQL_2、SQL_4)或(SQL_3、SQL_5)：我們可以輕易發現編號：SQL_2、SQL_4 之間唯一的差別是連接的類型不同：SQL_2為左連接，而SQL_4為等值連接，其他所有的都一樣，然後SQL執行後獲得的記錄集卻截然不一樣，並且SQL_2是對符合表left_table限制條件的記錄都作為最後的記錄集，而SQL_4則是對同時符合表left_table、表right_table限制條件的記錄才作為最後的記錄集；SQL_2與SQL_4之間對比獲得的結論，同樣適用於SQL_3與SQL_5之間，為此部再單獨描述；
編號(SQL_4、SQL_6)或(SQL_5、SQL_7)：對於等值連接操作，不管對right_table的限制條件,放ON字句，還是WHERE子句中，對記錄集影響都是一樣的；
編號SQL_3、SQL_5、SQL_8：SQL_5與SQL_3相比較我們可發現，用的連接關鍵字都是：左連接，但是ON子句中的表right_table限制條件被放到WHERE子句中，導致最後獲得的記錄集完全不一樣，而是跟等值連接SQL_8的記錄集一樣，通過比較可以發現SQL_5的記錄集不符合左連接的特性，而是符合等值連接的特性，所以此寫法不是左連接語句，而是相當於等值連接的語句；
編號SQL_2、SQL_3、SQL_9：SQl_9與SQL_2、SQL_3的連接關鍵字都是：左連接，其記錄集相比較可以發現限制表left_table的記錄條件：M.ID<=6沒有起作用，限制表right_table的記錄條件：N.SearchNum>300依然有效果，從左連接的特性角度分析，依然屬於左連接，只是寫法很混亂，容易誤導SQL的讀者。
