圖解MySQL 內連線、外連線、左連線、右連線、全連線……太多了

圖解MySQL 內連線、外連線、左連線、右連線、全連線……太多了

用兩個表(a_table、b_table),關聯欄位a_table.a_id和b_table.b_id來演示一下MySQL的內連線、外連線( 左(外)連線、右(外)連線、全(外)連線)。

MySQL版本:Server version: 5.6.31 MySQL Community Server (GPL)

資料庫表:a_table、b_table

主題:內連線、左連線(左外連線)、右連線(右外連線)、全連線(全外連線)

前提

建表語句:

CREATE TABLE `a_table` (
`a_id` int(11) DEFAULT NULL,
`a_name` varchar(10) DEFAULT NULL,
`a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `b_table` (
`b_id` int(11) DEFAULT NULL,
`b_name` varchar(10) DEFAULT NULL,
`b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表測試資料:

一、內連線

關鍵字:inner join on

語句:select * from a_table a inner join b_table bon
a.a_id = b.b_id;
執行結果:
說明:組合兩個表中的記錄,返回關聯欄位相符的記錄,也就是返回兩個表的交集(陰影)部分。

二、左連線(左外連線)

關鍵字:left join on / left outer join on
語句:select * from a_table a left join b_table bon
a.a_id = b.b_id;
執行結果:

說明:
left join 是left outer join的簡寫,它的全稱是左外連線,是外連線中的一種。
左(外)連線,左表(a_table)的記錄將會全部表示出來,而右表(b_table)只會顯示符合搜尋條件的記錄。右表記錄不足的地方均為NULL。

三、右連線(右外連線)

關鍵字:right join on / right outer join on
語句:select * from a_table a right outer join b_table b on a.a_id = b.b_id;
執行結果:

說明:
right join是right outer join的簡寫,它的全稱是右外連線,是外連線中的一種。
與左(外)連線相反,右(外)連線,左表(a_table)只會顯示符合搜尋條件的記錄,而右表(b_table)的記錄將會全部表示出來。左表記錄不足的地方均為NULL。

四、全連線(全外連線)

MySQL目前不支援此種方式,可以用其他方式替代解決。

五、補充,MySQL如何執行關聯查詢

MySQL認為任何一個查詢都是一次“關聯”,並不僅僅是一個查詢需要到兩個表匹配才叫關聯,所以在MySQL中,每一個查詢,每一個片段(包括子查詢,甚至基於單表查詢)都可以是一次關聯。
當前MySQL關聯執行的策略很簡單:MySQL對任何關聯都執行巢狀迴圈關聯操作,即MySQL先在一個表中迴圈取出單條資料,然後在巢狀迴圈到下一個表中尋找匹配的行,依次下去,直到找到所有表中匹配的行為止。然後根據各個表匹配的行,返回查詢中需要的各個列。請看下面的例子中的簡單的查詢:
查詢語句:select tbl1.col1, tbl2.col2 from tbl1 inner join tbl2 using(col3) where tbl1.col1 in (5, 6);
假設MySQL按照查詢中的表順序進行關聯操作,我們則可以用下面的虛擬碼表示MySQL將如何完成這個查詢:
outer_iter = iterator over tbl1 where col1 in (5, 6)
outer_row = outer_iter.next
while outer_row
inner_iter = iterator over tbl2 where col3 = outer_row.col3
inner_row = inner_iter.next
while inner_row
output [ outer_row.col1, inner_row.col2]
inner_row = inner_iter.next
end
outer_row = outer_iter.next
end

上面的執行計劃對於單表查詢和多表關聯查詢都適用,如果是一個單表查詢,那麼只需要上面外層的基本操作。對於外連線,上面的執行過程仍然適用。例如,我們將上面的查詢語句修改如下:
select tbl1.col1, tbl2.col2 from tbl1 left outer join tbl2 using(col3) where tbl1.col1 in (5, 6);

那麼,對應的虛擬碼如下:
outer_iter = iterator over tbl1 where col1 in (5, 6)
outer_row = outer_iter.next
while outer_row
inner_iter = iterator over tbl2 where col3 = outer_row.col3
inner_row = inner_iter.next
if inner_row
while inner_row
output [ outer_row.col1, inner_row.col2]
inner_row = inner_iter.next
end
else
output [ outer_row.col1, null]
end
outer_row = outer_iter.next
end

說明:第五部分摘自《高效能MySQL 第三版》