Hatena::ブログ(Diary)

noire722 の日記 RSSフィード

2010-06-10

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

◆表の結合◆

結合元テーブルAとB

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

A

KEY
K10
K20
K30
K40

B

IDKEYNAME
1K20AAA
2K30BBB
3K50CCC
4(NULL)DDD
5K10EEE
6K20FFF

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

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
IDKEYNAME
1K20AAA
2K30BBB
5K10EEE
6K20FFF

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
IDKEYNAME
1K20AAA
2K30BBB
5K10EEE
6K20FFF
(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
IDKEYNAME
1K20AAA
2K30BBB
3(NULL)CCC
4(NULL)DDD
5K10EEE
6K20FFF

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
IDKEYNAME
1K20AAA
2K30BBB
3(NULL)CCC
4(NULL)DDD
5K10EEE
6K20FFF
(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
IDKEYNAME
1K10AAA
1K20AAA
1K30AAA
1K40AAA
2K10BBB
2K20BBB
2K30BBB
2K40BBB
3K10CCC
3K20CCC
3K30CCC
3K40CCC
4K10DDD
4K20DDD
4K30DDD
4K40DDD
5K10EEE
5K20EEE
5K30EEE
5K40EEE
6K10FFF
6K20FFF
6K30FFF
6K40FFF

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
IDKEYNAME
1K10(NULL)
1K20AAA
1K30(NULL)
1K40(NULL)
2K10(NULL)
2K20(NULL)
2K30BBB
2K40(NULL)
3K10(NULL)
3K20(NULL)
3K30(NULL)
3K40(NULL)
4K10(NULL)
4K20(NULL)
4K30(NULL)
4K40(NULL)
5K10EEE
5K20(NULL)
5K30(NULL)
5K40(NULL)
6K10(NULL)
6K20FFF
6K30(NULL)
6K40(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

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/noire722/20100610/1276157320