[MySQL]left, right, inner, outer join 使用方法

表格 test1 資料表

表格 test2 資料表


首先大概是了解 inner 跟 outer 的差別，初學者大概都會使用 inner 這也是我們常常在用的 SQL，inner 就是 join 兩個資料表只顯示匹對的資料，另外一種 outer 就是不管是否有匹對，都會將資料顯示出來，又分為 LEFT, RIGHT, FULL join。

join 總共分為六種

Inner Join
Natural Join
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join

1. Inner Join

--
-- 這算是最普通的 join 方法
--
SELECT a.*, b.* FROM `test1` AS a, `test2` AS b WHERE a.id = b.id

2. Natural Join

--
-- 利用兩資料表相同欄位，自動連接上
SELECT a.*, b.* FROM `test1` AS a NATURAL JOIN `test2` AS b

3. Left, Right join

--
-- 這兩個其實是相同的，left join 就是顯示左邊表格所有資料，如果匹對沒有的話，就是顯示 NULL
-- right 則是相反
SELECT a.*, b.* FROM `test1` AS a LEFT JOIN `test2` AS b ON a.id = b.id

4. Full Outer Join
這個可以利用 SQL UNION 處理掉，這只是聯集 Left 跟 Right

5. Cross Join
在 MySQL 語法裡面，它相同於 INNER Join，但是在標準 SQL 底下，它們不盡相同

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

同等於

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

取一段 MySQL 官網的文字：

In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.