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

最近在高雄面試的時候,被問到的資料庫問題,什麼是 left join,out join,inner join,其實這些都是寫基本 SQL 語法需要知道的,當然我比較少用到 out join,不過還是要知道一下比較好喔,底下來說明一下這些,整理一些心得 表格 test1 資料表 2 (by appleboy46) 表格 test2 資料表 1 (by appleboy46) 首先大概是了解 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.
參考: http://dev.mysql.com/doc/refman/5.0/en/join.html http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html SQL Join語法筆記 http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html
  • 不好意思,可否請教
    SELECT a.*, b.* FROM `test1` AS a, `test2` AS b WHERE a.id = b.id
    上方a.* 是資料欄位名稱嗎
    那*是代表該資料欄位的所有紀錄嗎
    在 Join Taable 是否可以改用*來代表查詢所有欄位
    像SELECT * FROM Table 的語句
    感謝賜教

  • a.* 代表所有 a 資料表的欄位,盡量不要這樣寫,如果想講求效能
    就把使用的欄位寫出來,盡量不要用 * 去選取全部欄位

  • 我用2個 as 好像會出錯耶
    後來用1個 as 就OK了
    Select Table1.*, Table2.* as NewTable from Table1, Table2 where Table1.u_id = Table2.u_id
    謝謝

  • 你的用法錯誤 Table2.* 代表 Table2 所有欄位,然後你把它 as NewTable,變成多對一
    應該是 Table2.id as 自訂欄位名稱,這樣才對
    http://www.1keydata.com/tw/sql/sqlselect.html

  • 我可能會錯意了
    小弟是按照您上面第一個範例
    因為不知道a跟b各代表的是同一個資料表還是2個
    所以就隨便測看看
    後來我用這樣Join也可以耶
    SELECT Table1. * , Table2. * FROM Table2 INNER JOIN Table1 ON Table1.Table2_id = Table2.Table2_id
    謝謝您提供的網站
    我有看到了

  • myianUS

    HI 想請問若兩個資料表 除了主KEY以外還有相同欄位 當使用while時如何區別呢

  • myianUS

    HI 想請問若兩個資料表 除了主KEY以外還有相同欄位 當使用while時如何區別呢

  • 夏柏仁

    用表名指定
    有兩張表 TableA, TableB
    WHERE TableA.sth = TableB.sth AND TableA.id = TableB.id