JOINについて 2015/12

テスト用に以下の2つのテーブルを用意する。
奇数テーブル TBL_ODD
No (INT) Name (Text)
0 Zero
1 One
3 Three
5 Five
7 Seven
9 Nine
偶数テーブル TBL_EVEN
No (INT) Name (Text)
0 Zero
2 Two
4 Four
6 Six
8 Eight
10 Ten
Createスクリプト Createスクリプト

INNER JOIN 内部結合

SELECT A.no AS no_A, A.name AS name_A, B.no AS no_B, B.name AS name_B
FROM tbl_odd A
INNER JOIN tbl_even B ON A.no = B.no;
no_a name_a no_b name_b
0 Zero 0 Zero

LEFT JOIN 左外部結合

SELECT A.no AS no_A, A.name AS name_A, B.no AS no_B, B.name AS name_B
FROM tbl_odd A
LEFT JOIN tbl_even B ON A.no = B.no;
(LEFT JOIN は LEFT OUTER JOIN と書いても結果は同じ)
no_a name_a no_b name_b
0 Zero 0 Zero
1 One
3 Three
5 Five
7 Seven
9 Nine

RIGHT JOIN 右外部結合

SELECT A.no AS no_A, A.name AS name_A, B.no AS no_B, B.name AS name_B
FROM tbl_odd A
RIGHT JOIN tbl_even B ON A.no = B.no;
(RIGHT JOIN は RIGHT OUTER JOIN と書いても結果は同じ)
no_a name_a no_b name_b
0 Zero 0 Zero
2 Two
4 Four
6 Six
8 Eight
10 Ten

FULL JOIN 完全外部結合

SELECT A.no AS no_A, A.name AS name_A, B.no AS no_B, B.name AS name_B
FROM tbl_odd A
FULL JOIN tbl_even B ON A.no = B.no
ORDER BY no_a, no_b;
(FULL JOIN は FULL OUTER JOIN と書いても結果は同じ)
no_a name_a no_b name_b
0 Zero 0 Zero
1 One
3 Three
5 Five
7 Seven
9 Nine
2 Two
4 Four
6 Six
8 Eight
10 Ten

CROSS JOIN クロス結合

SELECT row_number() over() AS no, A.no AS no_A, A.name AS name_A, B.no AS no_B, B.name AS name_B
FROM tbl_odd A CROSS JOIN tbl_even B ORDER BY no_a, no_b;
no no_a name_a no_b name_b
1 0 Zero 0 Zero
2 0 Zero 2 Two
3 0 Zero 4 Four
4 0 Zero 6 Six
5 0 Zero 8 Eight
6 0 Zero 10 Ten
7 1 One 0 Zero
8 1 One 2 Two
9 1 One 4 Four
10 1 One 6 Six
11 1 One 8 Eight
12 1 One 10 Ten
13 3 Three 0 Zero
14 3 Three 2 Two
15 3 Three 4 Four
16 3 Three 6 Six
17 3 Three 8 Eight
18 3 Three 10 Ten
19 5 Five 0 Zero
20 5 Five 2 Two
21 5 Five 4 Four
22 5 Five 6 Six
23 5 Five 8 Eight
24 5 Five 10 Ten
25 7 Seven 0 Zero
26 7 Seven 2 Two
27 7 Seven 4 Four
28 7 Seven 6 Six
29 7 Seven 8 Eight
30 7 Seven 10 Ten
31 9 Nine 0 Zero
32 9 Nine 2 Two
33 9 Nine 4 Four
34 9 Nine 6 Six
35 9 Nine 8 Eight
36 9 Nine 10 Ten
すべての組み合わせが返される。2つのテーブルが6列ずつあるので、6×6=36通りの結果が返される。 
SELECT * FROM tbl_odd CROSS JOIN tbl_even;
と
SELECT * FROM tbl_odd, tbl_even;
は同じ直積集合を結果として返す。