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


從業以來,發現很多技術人員和同事向筆者或是在網絡上咨詢過LEFT JOIN 如何寫及其特性,而等值連接一般都沒問題,幾年前曾經寫過一份Microsoft SQL Server版本的,此次特意再寫一份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 | 06440350@qq.com |
|  2 | 25173782@qq.com |
|  3 | 66328120@qq.com |
|  4 | 16752438@qq.com |
|  5 | 92117196@qq.com |
|  6 | 02026078@qq.com |
    +—-+—————–+
  標準左連接


  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 | 06440350@qq.com |        817 |     39 |
|  2 | 25173782@qq.com |       NULL |   NULL |
|&n

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。