SQL JOIN types

SQL查詢時用JOIN語法連接多個資料表，JOIN有以下幾種形式。

1. INNER JOIN
2. LEFT OUTER JOIN
3. LEFT OUTER JOIN exclude common
4. RIGHT OUTER JOIN
5. RIGHT OUTER JOIN exclude common
6. FULL OUTER JOIN
7. FULL OUTER JOIN exclude common

例如現在有兩資料表T1及T2。

T1

+----+-------+
| ID | VALUE |
+----+-------+
| 1  | a     |
+----+-------+
| 2  | b     |
+----+-------+
| 3  | c     |
+----+-------+

T2

+----+-------+-------+
| ID | T1_ID | VALUE |
+----+-------+-------+
| 1  | 1     | v     |
+----+-------+-------+
| 2  | 1     | w     |
+----+-------+-------+
| 3  | 2     | x     |
+----+-------+-------+
| 4  | 2     | y     |
+----+-------+-------+
| 5  | 3     | z     |
+----+-------+-------+

下面為各JOIN範例。

INNER JOIN

SELECT * 
FROM T1 t1 INNER JOIN T2 t2 ON t1.ID = t2.T1_ID;

---

LEFT OUTER JOIN

SELECT * 
FROM T1 t1 LEFT OUTER JOIN T2 t2 ON t1.ID = t2.T1_ID;

---

LEFT OUTER JOIN exclude common

SELECT * 
FROM T1 t1 LEFT OUTER JOIN T2 t2 ON t1.ID = t2.T1_ID
WHERE t2.ID IS NULL;

---

RIGHT OUTER JOIN

SELECT * 
FROM T1 t1 RIGHT OUTER JOIN T2 t2 ON t1.ID = t2.T1_ID;

---

RIGHT OUTER JOIN exclude common

SELECT * 
FROM T1 t1 RIGHT OUTER JOIN T2 t2 ON t1.ID = t2.T1_ID
WHERE t1.ID IS NULL;

---

FULL OUTER JOIN

SELECT * 
FROM T1 t1 FULL OUTER JOIN T2 t2 ON t1.ID = t2.T1_ID;

---

FULL OUTER JOIN exclude common

SELECT * 
FROM T1 t1 FULL OUTER JOIN T2 t2 ON t1.ID = t2.T1_ID
WHERE t1.ID IS NULL
OR t2.ID IS NULL;