LEFT, RIGHT, FULL OUTER JOINの実験
公開日：
:
最終更新日：2014/02/04
未分類
記事内に広告を含む場合があります。記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
テストデータを用意
SELECT * FROM t5s;
id | name | t6_id
----+------+-------
1 | A | 2
2 | B | 4
3 | C | 6
4 | D | 8
5 | E | 10
6 | F | 12
7 | G | 14
8 | H | 16
9 | I | 18
10 | J | 20
(10 rows)
SELECT * FROM t6s;
id | name
----+------
1 | O
2 | P
3 | Q
4 | R
5 | S
6 | T
7 | U
8 | V
9 | W
10 | X
(10 rows)
LEFT OUTER JOIN
SELECT
*
FROM
t5s AS t5
LEFT OUTER JOIN
t6s AS t6
ON (
t5.t6_id = t6.id
)
;
id | name | t6_id | id | name
----+------+-------+----+------
1 | A | 2 | 2 | P
2 | B | 4 | 4 | R
3 | C | 6 | 6 | T
4 | D | 8 | 8 | V
5 | E | 10 | 10 | X
6 | F | 12 | |
7 | G | 14 | |
8 | H | 16 | |
9 | I | 18 | |
10 | J | 20 | |
(10 rows)
RIGHT OUTER JOIN
SELECT
*
FROM
t5s AS t5
RIGHT OUTER JOIN
t6s AS t6
ON (
t5.t6_id = t6.id
)
;
id | name | t6_id | id | name
----+------+-------+----+------
| | | 1 | O
1 | A | 2 | 2 | P
| | | 3 | Q
2 | B | 4 | 4 | R
| | | 5 | S
3 | C | 6 | 6 | T
| | | 7 | U
4 | D | 8 | 8 | V
| | | 9 | W
5 | E | 10 | 10 | X
(10 rows)
FULL OUTER JOIN
SELECT
*
FROM
t5s AS t5
FULL OUTER JOIN
t6s AS t6
ON (
t5.t6_id = t6.id
)
;
id | name | t6_id | id | name
----+------+-------+----+------
| | | 1 | O
1 | A | 2 | 2 | P
| | | 3 | Q
2 | B | 4 | 4 | R
| | | 5 | S
3 | C | 6 | 6 | T
| | | 7 | U
4 | D | 8 | 8 | V
| | | 9 | W
5 | E | 10 | 10 | X
6 | F | 12 | |
7 | G | 14 | |
8 | H | 16 | |
9 | I | 18 | |
10 | J | 20 | |
(15 rows)
