[SQL] 5 types of SQL JOIN

Posted on | Edited on | In |

ANSI-standard SQL有五種JOIN：

  1. INNER JOIN
  2. LEFT OUTER JOIN
  3. RIGHT OUTER JOIN
  4. FULL OUTER JOIN
  5. CROSS JOIN

A Visual Explanation of SQL Joins用Venn diagrams方式圖解JOIN。

範例：

1
2
3
4
5
6
7
TableA        TableB
id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja
  • INNER JOIN：

SQL：

1
2
3
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

結果：

1
2
3
4
5
TableA         TableB
id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja

Venn diagram：

列出兩個Table共有的資料，即兩個Table的交集。

  • LEFT OUTER JOIN：

SQL：

1
2
3
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

結果：

1
2
3
4
5
6
7
TableA         TableB
id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

Venn diagram：

以左邊Table為主，若沒有配對到資料，顯示null。

  • RIGHT OUTER JOIN：

跟LEFT OUTER JOIN大同小異，結果改成以Table B為主，不再贅述。

  • FULL OUTER JOIN：

SQL：

1
2
3
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

結果：

1
2
3
4
5
6
7
8
9
TableA           TableB
id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

Venn diagram：

為兩個Table的聯集，若沒有配對到資料以null顯示。

  • CROSS JOIN：
    即是Cartesian product，會產生兩個Table所有的組合。
    SQL：
    1
    2
    SELECT * FROM TableA
    CROSS JOIN TableB

結果：
4*4 = 16種組合

因為CROSS JOIN會將所有組合列出來，所以當資料量龐大時候，效能會變得很差。

總結：

可以利用以上的五種JOIN和過濾方式，對兩個集合進行交集、聯集、差集等操作。
SQL JOINS

參考資料：