タグ: PostgreSQL

SQLのテーブル結合は、以下の３種類の方法があります。

• 内部結合
• 外部結合
• クロス結合

取得したいデータの内容によって、結合の方法を使い分ける必要があります。

今回は、３種類の結合方法についてPostgreSQLを使って解説していきます。

環境情報

• DB：PostgreSQL9.6

テーブル定義と格納データ

解説で使用するテーブル定義と格納データは以下になります。

『役職テーブル』と『社員テーブル』の、２つのテーブルを想定します。

create table yakusyoku (
  yakusyoku_id serial not null
  , yakusyoku_name varchar(10) not null
  , constraint yakusyoku_pkey primary key (yakusyoku_id)
) ;

create table syain (
  syain_id serial not null
  ,yakusyoku_id integer
  , syain_name varchar(10) not null
  , constraint syain_pkey primary key (syain_id)
) ;

"yakusyoku_id"	"yakusyoku_name"
-----------------------------------
1				"部長"
2				"課長"
3				"係長"
4				"主任"

"syain_id"	"yakusyoku_id"	"syain_name"
----------------------------------------------
1			1				"山田部長"
2			2				"鈴木課長"
3			3				"佐藤係長"
4			3				"佐藤係長"

内部結合／依存関係ありのデータを取得する

内部結合は、依存関係（レコード間のリレーション）があるデータのみを取得します。
今回の例では、役職が付いている社員のみが取得するSQLを実現することができます。

SELECT
    A.yakusyoku_id	
    A.yakusyoku_name	
    B.syain_id	
    B.syain_name
FROM
    yakusyoku as A INNER JOIN syain as B 
ON 
    A.yakusyoku_id = B.yakusyoku_id;

"yakusyoku_id"	"yakusyoku_name"	"syain_id"	"syain_name"
-----------------------------------------------------------
1				"部長"				1			"山田部長"
2				"課長"				2			"鈴木課長"
3				"係長"				3			"佐藤係長"
3				"係長"				4			"佐藤係長"

外部結合／依存関係なしのデータも取得する

内部結合は、依存関係がないデータのみも取得します。
今回の例では、主任の社員はいないのですが、役職テーブルのメインテーブルとして外部結合をおこなっているので、主任レコードも取得できます。

SELECT
    A.yakusyoku_id	
    A.yakusyoku_name	
    B.syain_id	
    B.syain_name
FROM
    yakusyoku as A LEFT OUTER JOIN syain as B 
ON 
    A.yakusyoku_id = B.yakusyoku_id;

"yakusyoku_id"	"yakusyoku_name"	"syain_id"	"syain_name"
-----------------------------------------------------------------
1				"部長"				1			"山田部長"
2				"課長"				2			"鈴木課長"
3				"係長"				3			"佐藤係長"
3				"係長"				4			"佐藤係長"
4				"主任"				""			""

クロス結合／組み合わせを取得する

クロス結合の基本文法

クロス結合は、レコード同士の組み合わせを取得します。
今回の例では、全ての役職と全ての社員の組み合わせを取得できます。

結合条件も指定しないので、役職テーブルの主任レコードに対する組み合わせについても取得できます。

SELECT
    A.yakusyoku_id	
    A.yakusyoku_name	
    B.syain_id	
    B.syain_name
FROM
    yakusyoku as A CROSS JOIN syain as B;

"yakusyoku_id"	"yakusyoku_name"	"syain_id"	"syain_name"
--------------------------------------------------------------
1				"部長"				1			"山田部長"
1				"部長"				2			"鈴木課長"
1				"部長"				3			"佐藤係長"
1				"部長"				4			"佐藤係長"
2				"課長"				1			"山田部長"
2				"課長"				2			"鈴木課長"
2				"課長"				3			"佐藤係長"
2				"課長"				4			"佐藤係長"
3				"係長"				1			"山田部長"
3				"係長"				2			"鈴木課長"
3				"係長"				3			"佐藤係長"
3				"係長"				4			"佐藤係長"
4				"主任"				1			"山田部長"
4				"主任"				2			"鈴木課長"
4				"主任"				3			"佐藤係長"
4				"主任"				4			"佐藤係長"

クロス結合の使いどころ。横→縦変換

クロスジョインはあまり用途がないように思いますが、よく使う用途としては横→縦変換になります。

横→縦変換とは、カラム毎に格納されている値をレコード事に格納することです。
以下のようなイメージですね。

以下に具体的を使って説明しています。

テーブルの横→縦変換をおこなってみます。

create table col_test (
  col_test_id serial not null
  , name1 varchar(10) not null
  , name2 varchar(10) not null
  , name3 varchar(10) not null
  , name4 varchar(10) not null
  , name5 varchar(10) not null
  , constraint col_test_id_pkey primary key (col_test_id)
) ;

	"name1",	"name2",	"name3",	"name4",	"name5"
--------------------------------------------------------------
1,	"名称1_A",	"名称2_B",	"名称3_C",	"★名称4_D","名称5_E"
2,	"★名称1_B","名称2_B",	"名称3_B",	"名称4_B",	"名称5_B"
3,	"名称1_C",	"★名称2_C","★名称3_C","名称4_C",	"名称5_C"

このテーブルから、名称の先頭に「★」が付いている値を行データとして取得するとします。 この場合、縦→横変換をおこなうと便利です。

縦→横変換をおこなうために、テンポラリテーブル(TEMPORARY TABLE)
を使っています。
テンポラリテーブルで、1～5の値が入っているのみのテーブルを作成しています。

このテンポラリテーブルと対象テーブル（ここではpivot）をCROSS JOINを使って組み合わせを作成し、その組み合わせで名称の先頭に「★」ついているレコードのみを取得しています。

-- テンポラリテーブルの作成
CREATE TEMPORARY TABLE pivot (
 val integer PRIMARY KEY
);

-- テンポラリテーブルに1～5の値が入った状態にする
INSERT INTO pivot with recursive rec(val) as (
 values(1)
 union all
 select 
    Val+1
 from 
    rec
 where 
    Val+1 <= 5
) select Val from rec;

-- 名称の先頭に「★」がついたレコードのみを抽出
SELECT * FROM (
    SELECT
        case P.val
            when 1 then A.name1
            when 2 then A.name2
            when 3 then A.name3
            when 4 then A.name4
            when 5 then A.name5
        end as cross
    FROM
        col_test as A CROSS JOIN pivot P) AS X
WHERE
    X.cross like '★%';

"cross"
-------------------
★名称1_B
★名称2_C
★名称3_C
★名称4_D

Tweet