前言
對於關係型資料庫來說，對於複雜的查詢表連線是必不可少的，下面妹子就來介紹下表連線的方式
內連線（Inner Join）
Inner指的是 intersect相交,顧名思義就是取兩表的交集。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63條*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10條*/
/*inner-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a
INNER JOIN `b_borrow_deductions` AS b
ON a.per_id=b.per_id;/*51條*/表a有63條資料，表b有10條資料，兩表都有鍵值per_id，用INNER JOIN取交集為51條
外連線（Outer Join）
Outer指的是 union集合,就是取兩表的並集。
其中外連線分為3種：
Full outer join
full outer join 得到兩表的乘集,也可叫做笛卡爾乘積。如果a表中的行在b表中沒有對應的部分,b的部分將是 null, 反之亦然。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63條*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10條*/
/*full-outer-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a
JOIN `b_borrow_deductions` AS b /*630條*/取兩表的乘集，則a表的行數乘以b表的行數，63*10=630（條）
Left join
左連線，取表a的所有行，和b表中匹配的值，沒有匹配的則以null值取代。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63條*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10條*/
/*left-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a
LEFT JOIN `b_borrow_deductions` AS b
ON a.per_id=b.per_id;/*63條*/a表有63行，b表中鍵值per_id在a表中都有。
Right join
右連線，取表b的所有行，和a表中匹配的值，沒有匹配的則以null值取代。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63條*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10條*/
/*right-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a
RIGHT JOIN `b_borrow_deductions` AS b
ON a.per_id=b.per_id;/*51條*/b表有10行，a表中有重複鍵值per_id，所以有51條
反連線（Anti Join）
Anti指的是 英語詞根反對的意思，與inner join正好相反。
多用於!= not in 等查詢；如果找到滿足條件（!= not in）的不返回，不滿足條件（!= not in）的返回。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63條*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10條*/
/*anti-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a ,`b_borrow_deductions` AS b
WHERE a.per_id!=b.per_id;/*579條*/相當於a表與b表的乘級減去a表與b表的交集，即(63*10)-51=579條
半連線（Semi Join）
半連線多在子查詢exists中使用，找到存在的鍵值之後就不再查詢內部其他的鍵值了。
SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63條*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10條*/
/*semi-join */
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a
WHERE EXISTS(
SELECT NULL FROM `b_borrow_deductions` AS b WHERE a.per_id=b.per_id)/*51條*/
星型連線（Star Join）
是指一張大的事實表和多個小表關聯得出所需的資料，往四周發散，所以稱為星型連結。
要使用星型轉換，必須要求初始化引數star_transforation_enabled設為TRUE。
SELECT ...
FROM really_big_fact f -- 50 million rows
, dim1 d1 -- 50 rows
, dim2 d2 -- 100 rows
, dim3 d3 -- 10 rows
, dim4 d4 -- 15 rows
WHERE f.key1 = d1.key1
AND f.key2 = d2.key2
AND f.key3 = d3.key3
AND f.key4 = d4.key4現在只有
Oracle才有
錶連結的方式
錶連結的方式有：排序合併連線(Sort Merge join)、巢狀迴圈連線(Nested Logo join)、雜湊連線(Mash join)、笛卡爾連結(Cross join)
參考資料
Happy coding .. ?