◆SQLメモ:別テーブルからの参照の有無: 万象酔歩

◆SQLメモ:別テーブルからの参照の有無

SQL、すぐに忘れる。で、メモ。
このメモよりさらに基本のメモを SQLど忘れメモに置いてある。

別テーブルからの参照の有無(最終形）
単純なOUTER JOIN
OUTER JOIN＋GROUP BY
OUTER JOIN＋GROUP BY+COUNT
OUTER JOIN＋GROUP BY+CASE式COUNT
OUTER JOIN＋GROUP BY+CASE文(IS NULL)
INNER JOIN (参考)
レコード生成SQL (参考)

別テーブルからの参照の有無(最終形）

単純なテーブルの組み合わせは単純にJOINで可能ですが、例えば、参照があるかないかといった、ある意味もっと単純なものを出そうとすると、工夫が必要となります。

ここでは、２つのテーブル（personとrelation)があり personレコードの関連をrelationレコードで示しています。

mysql> select * from person;
+--------+------+------+
| id     | name | type |
+--------+------+------+
| ID0001 | A    | X    |
| ID0002 | B    | X    |
| ID0003 | C    | Y    |
| ID0004 | D    | Y    |
| ID0005 | E    | Y    |
| ID0006 | F    | Y    |
| ID0007 | G    | Z    |
| ID0008 | H    | Z    |
| ID0009 | I    | Z    |
+--------+------+------+
mysql> select * from relation;
+--------+---------+---------+
| id     | parent1 | parent2 |
+--------+---------+---------+
| ID0004 | ID0001  | ID0002  |
| ID0005 | ID0001  | ID0004  |
| ID0006 | ID0002  | ID0003  |
| ID0007 | ID0005  | ID0003  |
| ID0008 | ID0005  | ID0007  |
| ID0009 | ID0005  | ID0007  |
+--------+---------+---------+
この例ではD(ID0004)はA(ID0001)とB(ID0002)
の子であることを示している。

この中からtype=Yのpersonの子の有り無しを取得します。

SELECT  person.id
       ,person.name
       ,CASE WHEN
          relation.id IS NULL THEN 0
          ELSE                     1
        END as has_child
  FROM  person
  LEFT OUTER JOIN relation
       ON relation.parent1=person.id 
       OR relation.parent2=person.id 
  WHERE type='Y'
  GROUP BY person.id
  ORDER BY person.name;
+--------+------+-----------+
| id     | name | has_child |
+--------+------+-----------+
| ID0003 | C    |         1 |
| ID0004 | D    |         1 |
| ID0005 | E    |         1 |
| ID0006 | F    |         0 |
+--------+------+-----------+

OUTER JOINした上で、グループ化し、有る/無しを判定し表示しています。

postgreSQLではこのSQL文は動きません。次のSQLとなります

SELECT DISTINCT
        person.id
       ,person.name
       ,CASE WHEN
          relation.id IS NULL THEN 0
          ELSE                     1
        END as has_child
  FROM  person
  LEFT OUTER JOIN relation
       ON relation.parent1=person.id 
       OR relation.parent2=person.id 
  WHERE type='Y'
  -- GROUP BY person.id,person.name,relation.id
  ORDER BY person.name;

以下で単純操作から順次この形に至る過程を示します。

単純なOUTER JOIN

単純にOUTER JOINを行った場合、親子の組み合わせ分データが得られます。
この例ではCのデータが２つ、Eのデータが３つ得られています。

SELECT  person.id
　     ,person.name
　     ,relation.id
  FROM  person
  LEFT OUTER JOIN relation
       ON relation.parent1=person.id
       OR relation.parent2=person.id
  WHERE type='Y';
+--------+------+--------+
| id     | name | id     |
+--------+------+--------+
| ID0004 | D    | ID0005 |
| ID0003 | C    | ID0006 |
| ID0003 | C    | ID0007 |
| ID0005 | E    | ID0007 |
| ID0005 | E    | ID0008 |
| ID0005 | E    | ID0009 |
| ID0006 | F    | NULL   |
+--------+------+--------+

Fは子を持ちませんので子のidはnullとなっています。

なおLEFT OUTER JOINの「LEFT」は"JOIN"の「左側」に記述されたテーブル（ここではperson)レコードにカラムを追加するという意味です。

OUTER JOIN＋GROUP BY

person.idでグループ化することにより、同じperson.idを持つデータが一つにまとめられます。

SELECT  person.id
       ,person.name
       ,relation.id
  FROM  person
  LEFT OUTER JOIN relation
       ON relation.parent1=person.id
       OR relation.parent2=person.id
  WHERE type='Y'
  GROUP BY person.id;
+--------+------+--------+
| id     | name | id     |
+--------+------+--------+
| ID0003 | C    | ID0006 |
| ID0004 | D    | ID0005 |
| ID0005 | E    | ID0007 |
| ID0006 | F    | NULL   |
+--------+------+--------+

postgreSQLではこのSQL文は動きません。

OUTER JOIN＋GROUP BY+COUNT

COUNT関数で要素数を得ることができます。

SELECT  person.id
       ,person.name
       ,COUNT(relation.id) as num_of_children
  FROM  person
  LEFT OUTER JOIN relation
       ON relation.parent1=person.id
       OR relation.parent2=person.id
  WHERE type='Y'
  GROUP BY person.id;
+--------+------+-----------------+
| id     | name | num_of_children |
+--------+------+-----------------+
| ID0003 | C    |               2 |
| ID0004 | D    |               1 |
| ID0005 | E    |               3 |
| ID0006 | F    |               0 |
+--------+------+-----------------+

postgreSQLではこのSQL文は動きません。次のSQLとなります

SELECT DISTINCT
        person.id
       ,person.name
       ,COUNT(relation.id) as num_of_children
  FROM  person
  LEFT OUTER JOIN relation
       ON relation.parent1=person.id
       OR relation.parent2=person.id
  WHERE type='Y'
  GROUP BY person.id,person.name,relation.id;

OUTER JOIN＋GROUP BY+CASE式COUNT

CASE式で0と0以外に分ける。

SELECT  person.id
       ,person.name
       ,CASE COUNT(relation.id)
          WHEN 0 THEN 0
          ELSE        1
        END as has_child
  FROM  person
  LEFT OUTER JOIN relation
       ON relation.parent1=person.id 
       OR relation.parent2=person.id 
  WHERE type='Y'
  GROUP BY person.id;
+--------+------+-----------+
| id     | name | has_child |
+--------+------+-----------+
| ID0003 | C    |         1 |
| ID0004 | D    |         1 |
| ID0005 | E    |         1 |
| ID0006 | F    |         0 |
+--------+------+-----------+

postgreSQLではこのSQL文は動きません。次のSQLとなります

SELECT DISTINCT
        person.id
       ,person.name
       ,CASE COUNT(relation.id)
          WHEN 0 THEN 0
          ELSE        1
        END as has_child
  FROM  person
  LEFT OUTER JOIN relation
       ON relation.parent1=person.id 
       OR relation.parent2=person.id 
  WHERE type='Y'
  GROUP BY person.id,person.name,relation.id;

OUTER JOIN＋GROUP BY+CASE文(IS NULL)

relation.idが有るかnullかで子の有る無しを判定し表示すれば最初に示したデータが得られます。

SELECT  person.id
       ,person.name
       ,CASE WHEN
           relation.id IS NULL THEN 0
                       ELSE         1
        END as has_child
  FROM  person
  LEFT OUTER JOIN relation
       ON relation.parent1=person.id
       OR relation.parent2=person.id
  WHERE type='Y'
  GROUP BY person.id;
+--------+------+-----------+
| id     | name | has_child |
+--------+------+-----------+
| ID0003 | C    |         1 |
| ID0004 | D    |         1 |
| ID0005 | E    |         1 |
| ID0006 | F    |         0 |
+--------+------+-----------+

CASE文でIS NULLを使う場合
CASE 要素 WHEN ... THEN
という構文ではなく
CASE WHEN 要素 IS NULL THEN
という形になることに注意が必要です。

COUNTより高速となる可能性があります。

postgreSQLではこのSQL文は動きません。次のSQLとなります

SELECT DISTINCT
        person.id
       ,person.name
       ,CASE WHEN
           relation.id IS NULL THEN 0
                       ELSE         1
        END as has_child
  FROM  person
  LEFT OUTER JOIN relation
       ON relation.parent1=person.id
       OR relation.parent2=person.id
  WHERE type='Y'
  GROUP BY person.id,person.name,relation.id;

INNER JOIN (参考)

INNER JOINを用いた場合、子の存在しないレコード（ここではF)が得られません。
ついでに子の名前も出しました。親のタイプが'Y'の表ですので、HとIは片親しか出ていません。

SELECT  person.id
       ,person.name as parent
       ,relation.id
       ,p2.name     as child
  FROM  person
  INNER JOIN relation
        ON relation.parent1=person.id 
        OR relation.parent2=person.id 
  INNER JOIN person p2
        ON relation.id=p2.id
  WHERE person.typex='Y';
+--------+--------+--------+-------+
| id     | parent | id     | child |
+--------+--------+--------+-------+
| ID0004 | D      | ID0005 | E     |
| ID0003 | C      | ID0006 | F     |
| ID0003 | C      | ID0007 | G     |
| ID0005 | E      | ID0007 | G     |
| ID0005 | E      | ID0008 | H     |
| ID0005 | E      | ID0009 | I     |
+--------+--------+--------+-------+

レコード生成SQL (参考)

本メモでのレコード生成を行うSQLを載せます。

DROP   TABLE person;
DROP   TABLE relation;
CREATE TABLE person
          (id varchar(30),name varchar(30),type varchar(30));
CREATE TABLE relation
          (id varchar(30),parent1 varchar(30),parent2 varchar(30));
INSERT INTO person
           (id,name,type) VALUES('ID0001','A','X');
INSERT INTO person
           (id,name,type) VALUES('ID0002','B','X');
INSERT INTO person
           (id,name,type) VALUES('ID0003','C','Y');
INSERT INTO person
           (id,name,type) VALUES('ID0004','D','Y');
INSERT INTO person
           (id,name,type) VALUES('ID0005','E','Y');
INSERT INTO person
           (id,name,type) VALUES('ID0006','F','Y');
INSERT INTO person
           (id,name,type) VALUES('ID0007','G','Z');
INSERT INTO person
           (id,name,type) VALUES('ID0008','H','Z');
INSERT INTO person
           (id,name,type) VALUES('ID0009','I','Z');
INSERT INTO relation
           (id,parent1,parent2) VALUES('ID0004','ID0001','ID0002');
INSERT INTO relation
           (id,parent1,parent2) VALUES('ID0005','ID0001','ID0004');
INSERT INTO relation
           (id,parent1,parent2) VALUES('ID0006','ID0002','ID0003');
INSERT INTO relation 
           (id,parent1,parent2) VALUES('ID0007','ID0005','ID0003');
INSERT INTO relation
           (id,parent1,parent2) VALUES('ID0008','ID0005','ID0007');
INSERT INTO relation
           (id,parent1,parent2) VALUES('ID0009','ID0005','ID0007');

windowsコマンドプロンプト操作メモ

2015年9月20日 (日) パソコン・インターネット | 固定リンク
Tweet

トラックバック

この記事へのトラックバック一覧です： ◆SQLメモ:別テーブルからの参照の有無:

コピー	:		コントロールＣ
ペースト	:		右クリック

万象酔歩

あれやこれやと

携帯URL

最近の記事

◆SQLメモ:別テーブルからの参照の有無

トラックバック