読者です 読者をやめる 読者になる 読者になる

SQL結合関連まとめ - INNER JOIN, (NATURAL) [LEFT | RIGHT] (OUTER) JOIN, UNION, INTERSECT, EXCEPT

◆表の結合◆

結合元テーブルAとB

----------------------------------------------------------
A

KEY
K10
K20
K30
K40

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

KEY
K10
K20
K30
K40

C

KEY
C10
C20
C30
K10
K20

----------------------------------------------------------

NATURAL LEFT (OUTER) JOIN

SELECT *
FROM A
  NATURAL LEFT JOIN C
KEY
K10
K20
K30
K40

NATURAL RIGHT (OUTER) JOIN

SELECT *
FROM A
  NATURAL RIGHT JOIN C
KEY
K10
K20
C20
C30
C10


◆クエリの結合◆

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
KEY
K10
K20

EXCEPT(= MINUS) 差集合

SELECT * FROM A
EXCEPT
SELECT * FROM C
KEY
K30
K40