◆表の結合◆
結合元テーブルAとB
----------------------------------------------------------
A
B
ID |
KEY |
NAME |
1 |
K20 |
AAA |
2 |
K30 |
BBB |
3 |
K50 |
CCC |
4 |
(NULL) |
DDD |
5 |
K10 |
EEE |
6 |
K20 |
FFF |
----------------------------------------------------------
INNER JOIN
SELECT B.ID, A.KEY, B.NAME
FROM A
INNER JOIN B ON A.KEY = B.KEY
ORDER BY B.ID, A.KEY, B.NAME
ID |
KEY |
NAME |
1 |
K20 |
AAA |
2 |
K30 |
BBB |
5 |
K10 |
EEE |
6 |
K20 |
FFF |
LEFT (OUTER) JOIN
SELECT B.ID, A.KEY, B.NAME
FROM A
LEFT JOIN B ON A.KEY = B.KEY
ORDER BY B.ID, A.KEY, B.NAME
ID |
KEY |
NAME |
1 |
K20 |
AAA |
2 |
K30 |
BBB |
5 |
K10 |
EEE |
6 |
K20 |
FFF |
(NULL) |
K40 |
(NULL) |
RIGHT (OUTER) JOIN
SELECT B.ID, A.KEY, B.NAME
FROM A
RIGHT JOIN B ON A.KEY = B.KEY
ORDER BY B.ID, A.KEY, B.NAME
ID |
KEY |
NAME |
1 |
K20 |
AAA |
2 |
K30 |
BBB |
3 |
(NULL) |
CCC |
4 |
(NULL) |
DDD |
5 |
K10 |
EEE |
6 |
K20 |
FFF |
FULL (OUTER) JOIN
SELECT B.ID, A.KEY, B.NAME
FROM A
FULL JOIN B ON A.KEY = B.KEY
ORDER BY B.ID, A.KEY, B.NAME
ID |
KEY |
NAME |
1 |
K20 |
AAA |
2 |
K30 |
BBB |
3 |
(NULL) |
CCC |
4 |
(NULL) |
DDD |
5 |
K10 |
EEE |
6 |
K20 |
FFF |
(NULL) |
K40 |
(NULL) |
CROSS JOIN
SELECT B.ID, A.KEY, B.NAME
FROM A
CROSS JOIN B
ORDER BY B.ID, A.KEY, B.NAME
ID |
KEY |
NAME |
1 |
K10 |
AAA |
1 |
K20 |
AAA |
1 |
K30 |
AAA |
1 |
K40 |
AAA |
2 |
K10 |
BBB |
2 |
K20 |
BBB |
2 |
K30 |
BBB |
2 |
K40 |
BBB |
3 |
K10 |
CCC |
3 |
K20 |
CCC |
3 |
K30 |
CCC |
3 |
K40 |
CCC |
4 |
K10 |
DDD |
4 |
K20 |
DDD |
4 |
K30 |
DDD |
4 |
K40 |
DDD |
5 |
K10 |
EEE |
5 |
K20 |
EEE |
5 |
K30 |
EEE |
5 |
K40 |
EEE |
6 |
K10 |
FFF |
6 |
K20 |
FFF |
6 |
K30 |
FFF |
6 |
K40 |
FFF |
PARTITIONED OUTER JOIN [Oracle10g]
SELECT B.ID, A.KEY, B.NAME
FROM A
LEFT JOIN B PARTITION BY (B.ID) ON A.KEY = B.KEY
ORDER BY B.ID, A.KEY, B.NAME
ID |
KEY |
NAME |
1 |
K10 |
(NULL) |
1 |
K20 |
AAA |
1 |
K30 |
(NULL) |
1 |
K40 |
(NULL) |
2 |
K10 |
(NULL) |
2 |
K20 |
(NULL) |
2 |
K30 |
BBB |
2 |
K40 |
(NULL) |
3 |
K10 |
(NULL) |
3 |
K20 |
(NULL) |
3 |
K30 |
(NULL) |
3 |
K40 |
(NULL) |
4 |
K10 |
(NULL) |
4 |
K20 |
(NULL) |
4 |
K30 |
(NULL) |
4 |
K40 |
(NULL) |
5 |
K10 |
EEE |
5 |
K20 |
(NULL) |
5 |
K30 |
(NULL) |
5 |
K40 |
(NULL) |
6 |
K10 |
(NULL) |
6 |
K20 |
FFF |
6 |
K30 |
(NULL) |
6 |
K40 |
(NULL) |
結合元テーブルA と C
----------------------------------------------------------
A
C
----------------------------------------------------------
NATURAL LEFT (OUTER) JOIN
SELECT *
FROM A
NATURAL LEFT JOIN C
NATURAL RIGHT (OUTER) JOIN
SELECT *
FROM A
NATURAL RIGHT JOIN C
◆クエリの結合◆
UNION 和集合
SELECT * FROM A
UNION
SELECT * FROM C
KEY |
C10 |
C20 |
C30 |
K10 |
K20 |
K30 |
K40 |
UNION ALL
SELECT * FROM A
UNION ALL
SELECT * FROM C
KEY |
K10 |
K20 |
K30 |
K40 |
C10 |
C20 |
C30 |
K10 |
K20 |
INTERSECT 積集合
SELECT * FROM A
INTERSECT
SELECT * FROM C
EXCEPT(= MINUS) 差集合
SELECT * FROM A
EXCEPT
SELECT * FROM C