SQL join語句總結

微信公眾號：PHP在線

早上一起床就刷微信(真想拍死自己)，看到一篇很有意思的推送，名為《圖解SQL join語句》，講的是用Venn圖圖解SQL join語句。看的過程中有點疑惑，乾脆就用MySQL實踐一下，順帶複習下MySQL嘛。這一實踐下來，還發現了另外的有趣的問題，比如MySQL中並沒有full outer join, 那要怎麼在MySQL中模擬full outer join的問題。這麼一來二去，涉及的內容就更加有意思了，不記下來有點可惜，另外國慶實在是一個放鬆的好時機啊，又想偷懶了，那就寫博客來防止自己偷懶吧！
由於寫出排版美觀的博客比較費時間，所以這一系列的博客可能比較潦草，希望各位看官海涵。

為了防止遺漏，先直接上各種連接對應的Venn圖，圖片引自推送，原作者已不可考。另外，自然連接是無法用Venn圖表示的。

假設有兩個表，A表，B表，表結構與數據如下：

複習MySQL：

進入MySQL的命令：mysql -u root -p 接著輸入密碼
查看資料庫：show databases; 注意複數，有分號
查看資料庫表：show tables;   注意複數，有分號
創建表：create table A(id int, name varchar(32));
插入數據: insert into A values(1, 'Pirate'); 注意字符串加單引號，另外如果只是插入部分欄位，只需在表名後打括號，指定欄位名，欄位名無需加單引號

內連接 inner join：只生成匹配連接條件的記錄，最常見沒啥好講。對應Venn圖的求交。

全外連接 full outer join： 會生產表A與表B的記錄全集，包含兩邊都匹配的記錄（inner join的結果集），另外如果有一邊沒有匹配，缺失的一邊為null。然而，請注意，全外連接與產生笛卡爾積結果集的自然連接是不同的！全外連接可以理解為left join與right join的併集。對應Venn圖求並。

由於MySQL中沒有提供full outer join關鍵字，我們需要用left join 與right join 再結合 union來模擬full outer join，用union當然是因為union會去重。

注意一定是A left join B union  A right join B， 這樣union 的結果才會是真正的模擬到full outer join。(而不是A left join B  union  B left join A，即兩次查詢表的順序要相同，因為union在去重的時候，只是單純的看每一行是否完全相同， 1 Pirate 2 Pirate 與 2 Pirate 1Pirate會被視為不同的記錄(廢話)，儘管我們人腦一看就知道這是一條重複的記錄）

左連接 left join：生成左表的全部記錄，對於右表中沒有匹配的記錄，用null補。對應Venn圖就是左集合。

左連接 right join：生成右表的全部記錄，對於左表中沒有匹配的記錄，用null補。對應Venn圖就是右集合。

比較有趣並引起我的疑惑的是一開始給出的圖中，關於下面這張圖的SQL的寫法，讓我發現了自己一直以來在SQL語句執行順序中的一個誤區。

推送的說法是「為了生成只在表A裡而不在表B中的記錄，我們同樣使用left join， 再用where B.id is null 來排除A與B的交集 」。一直以來我以為where B.id is null是作用在from B的集上的，事實證明我是錯的。 在join後面的where是作用在join on篩選後的結果集上的。
這裡附上SQL語句的執行順序，明確SQL語句的執行順序很重要。

(8)SELECT (9)DISTINCT (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list> 
邏輯查詢處理階段簡介
FROM：對FROM子句中的前兩個表執行笛卡爾積（Cartesian product)(交叉聯接），生成虛擬表VT1
ON：對VT1應用ON篩選器。只有那些使<join_condition>為真的行才被插入VT2。
OUTER(JOIN)：如 果指定了OUTER JOIN（相對於CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部聯接把左表標記為保留表，右外部聯接把右表標記為保留表，完全外部聯接把兩個表都標記為保留表）中未找到匹配的行將作為外部行添加到 VT2,生成VT3.如果FROM子句包含兩個以上的表，則對上一個聯接生成的結果表和下一個表重複執行步驟1到步驟3，直到處理完所有的表為止。
WHERE：對VT3應用WHERE篩選器。只有使<where_condition>為true的行才被插入VT4.
GROUP BY：按GROUP BY子句中的列列表對VT4中的行分組，生成VT5.
CUBE|ROLLUP：把超組(Suppergroups)插入VT5,生成VT6.
HAVING：對VT6應用HAVING篩選器。只有使<having_condition>為true的組才會被插入VT7.
SELECT：處理SELECT列表，產生VT8.
DISTINCT：將重複的行從VT8中移除，產生VT9.
ORDER BY：將VT9中的行按ORDER BY 子句中的列列表排序，生成遊標（VC10).
TOP：從VC10的開始處選擇指定數量或比例的行，生成表VT11,並返回調用者。