SQL查詢時用JOIN語法連接多個資料表,JOIN有以下幾種形式。
- INNER JOIN
- LEFT OUTER JOIN
- LEFT OUTER JOIN exclude common
- RIGHT OUTER JOIN
- RIGHT OUTER JOIN exclude common
- FULL OUTER JOIN
- 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;
沒有留言:
張貼留言