程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 剖析MySQL左連接/右連接/等值連接異同

剖析MySQL左連接/右連接/等值連接異同

編輯:關於MYSQL數據庫
 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=XXX
WHERE N.columnname=XXX…..

   等值連接

SELECT M.columnname……,N.* columnname…..
FROM left_table M [INNER] JOIN right_table N
ON M. columnname_join=N. columnname_join
WHERE M.columnname=XXX….. AND N.columnname=XXX….

或者
SELECT M.columnname……,N.
* columnname…..
FROM left_table M  ,  right_table N
WHERE 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,AlterDate
FROM 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的讀者。

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved