同じテーブル同士をJOINしたりしてみる
公開日：
:
最終更新日：2013/11/12
SQL
記事内に広告を含む場合があります。記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
こんなテーブルを用意
SELECT c1, c2 FROM t1s;
c1 | c2
----+----
AA | 11
(1 row)
SELECT c1, c2 FROM t2s;
c1 | c2
----+----
AA | 33
AA | 44
(2 rows)
SELECT c1, c2 FROM t3s;
c1 | c2
----+----
AA | 55
AA | 66
(2 rows)
t1とt1をJOIN
SELECT t1s.c1, t1s.c2, t2s.c1, t2s.c2
FROM t1s
JOIN t1s AS t2s
ON (t1s.c1 = t2s.c1);
c1 | c2 | c1 | c2
----+----+----+----
AA | 11 | AA | 11
(1 row)
t1とt2をJOIN
SELECT t1s.c1, t1s.c2, t2s.c1, t2s.c2
FROM t1s
JOIN t2s AS t2s
ON (t1s.c1 = t2s.c1);
c1 | c2 | c1 | c2
----+----+----+----
AA | 11 | AA | 44
AA | 11 | AA | 33
(2 rows)
t1とt2とt1をJOIN
SELECT t1s.c1, t1s.c2, t2s.c1, t2s.c2, t3s.c1, t3s.c2
FROM t1s
JOIN t2s AS t2s
ON (t1s.c1 = t2s.c1)
JOIN t1s
AS t3s ON (t1s.c1 = t3s.c1);
c1 | c2 | c1 | c2 | c1 | c2
----+----+----+----+----+----
AA | 11 | AA | 44 | AA | 11
AA | 11 | AA | 33 | AA | 11
(2 rows)
t1とt2とt3をJOIN
SELECT t1s.c1, t1s.c2, t2s.c1, t2s.c2, t3s.c1, t3s.c2
FROM t1s
JOIN t2s AS t2s
ON (t1s.c1 = t2s.c1)
JOIN t3s AS t3s
ON (t1s.c1 = t3s.c1);
c1 | c2 | c1 | c2 | c1 | c2
----+----+----+----+----+----
AA | 11 | AA | 44 | AA | 66
AA | 11 | AA | 33 | AA | 66
AA | 11 | AA | 44 | AA | 55
AA | 11 | AA | 33 | AA | 55
(4 rows)
t2とt3をJOIN
SELECT t2s.c1, t2s.c2, t3s.c1, t3s.c2
FROM t2s
JOIN t3s
ON (t2s.c1 = t3s.c1);
c1 | c2 | c1 | c2
----+----+----+----
AA | 33 | AA | 66
AA | 33 | AA | 55
AA | 44 | AA | 66
AA | 44 | AA | 55
(4 rows)
t2をDISTINCT、t3をDISTINCT、それからJOIN
SELECT DISTINCT ON (c1) c1, c2
FROM t2s;
c1 | c2
----+----
AA | 33
(1 row)
SELECT DISTINCT ON (c1) c1, c2
FROM t3s;
c1 | c2
----+----
AA | 55
(1 row)
SELECT *
FROM (SELECT DISTINCT ON (c1) c1, c2 FROM t2s) AS t2s
JOIN (SELECT DISTINCT ON (c1) c1, c2 FROM t3s) AS t3s
ON (t2s.c1 = t3s.c1);
c1 | c2 | c1 | c2
----+----+----+----
AA | 33 | AA | 55
関連記事
- PREV
- COALESCE 関数
- NEXT
- GROUP BYの実験
