INNER JOINとLEFT OUTER JOINの実験
公開日：
:
最終更新日：2014/01/31
SQL
記事内に広告を含む場合があります。記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
テーブル定義
postgres_test_development=# \d test1s
Table "public.test1s"
Column | Type | Modifiers
--------+------------------------+-----------------------------------------------------
id | integer | not null default nextval('test1s_id_seq'::regclass)
c1 | character varying(255) |
c2 | character varying(255) |
Indexes:
"test1s_pkey" PRIMARY KEY, btree (id)
postgres_test_development=# \d test2s
Table "public.test2s"
Column | Type | Modifiers
--------+------------------------+-----------------------------------------------------
id | integer | not null default nextval('test2s_id_seq'::regclass)
c1 | character varying(255) |
c2 | character varying(255) |
Indexes:
"test2s_pkey" PRIMARY KEY, btree (id)
レコード
postgres_test_development=# SELECT * FROM test1s;
id | c1 | c2
----+-----+-----
1 | AAA | BBB
2 | CCC | DDD
3 | EEE |
(3 rows)
postgres_test_development=# SELECT * FROM test2s;
id | c1 | c2
----+-----+-----
1 | BBB | 111
2 | BBB | 222
(2 rows)
INNER JOIN
postgres_test_development=# SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM test1s AS t1 INNER JOIN test2s AS t2 ON ( t1.c2 = t2.c1 );
c1 | c2 | c1 | c2
-----+-----+-----+-----
AAA | BBB | BBB | 222
AAA | BBB | BBB | 111
(2 rows)
LEFT OUTER JOIN
postgres_test_development=# SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM test1s AS t1 LEFT OUTER JOIN test2s AS t2 ON ( t1.c2 = t2.c1 );
c1 | c2 | c1 | c2
-----+-----+-----+-----
AAA | BBB | BBB | 222
AAA | BBB | BBB | 111
CCC | DDD | |
EEE | | |
(4 rows)
