SQLの結合3パターン。クロスジョインはカラム列からレコード行への変換で使おう。

データベース

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


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

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


今回は、3種類の結合方法について、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				"佐藤係長"

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


内部結合は、依存関係(データ間のリレーション)があるデータのみを取得します。
今回の例では、役職が付いている社員のみが取得できることになります。


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


Windows環境でNode.JSとPostgreSQLを接続してクエリ発行


前回、Windows上にNode.jsの環境を構築して、Hello,Worldを出力されるところまでおこないました。



今回は、Node.jsとPostgreSQLを接続して、データベースに格納されているデータをブラウザ上に表示させるところまでおこないたいと思います。


やっぱり、データベースに接続してデータを表示するというところまでは、お勉強としておさえておきたいところです。


環境情報


  • OS:Windows10
  • Webサーバ:Node.js 12.16.3
  • DB:PostgreSQL9.6

PostgreSQLとは


PostgreSQL(ぽすとぐれすきゅーえる)は、オープンソースのデータベースになります。
よく、「ぽすぐれ」と言われています。


PostgreSQLはオープンソースなので、気軽に使えるのが良いところです。
かつ、標準SQL にならった実装になっていますので、標準SQLでほぼ全てのクエリを実行することができます。


この記事では、PostgreSQLのダウンロードと環境構築の紹介は省略します。


node-postgres


Node.jsでPostgreSQLと接続をおこなうためには、「node-postgres」を使用する必要があります。
「node-postgres」は、npmでインストールをおこなうことができます。

任意のフォルダを作成して、そのフォルダで以下のコマンドを実行します。


npm install --save pg

インストールが完了すると、インストールしたフォルダに「node_modules」が作成され、インストールが完了します。
このフォルダにプログラムを作成していきます。


PostgreSQLに接続してデータ表示


実際にNode.jsのプログラムを作成して動かしてみます。
データベースにはあらかじめて、以下のデータを格納しておきます。

person_id

name

kana

gender

1

山田太郎

やまだたろう

1

2

山田次郎

やまだじろう

1

3

山田三郎

やまださぶろう

2


上記のデータをクエリで取得し、ブラウザ上に表示するプログラムを作成します。
プログラムとしては以下になります。


// モジュールを準備
var http = require('http')
var {Client} = require('pg'); 

http.createServer(function (req, res) {

    // PostgreSQLに接続
    var client = new Client({
        user: 'postgres',
        host: 'localhost',
        database: 'nodeJsDB',
        password: 'postgres',
        port: 5432
    })
    client.connect()

    // HTTPレスポンスヘッダを出力
    res.writeHead(200, {'Content-Type': 'text/html;charset=utf-8'})

    // クエリを実行してTABLEタグで出力
    var query = client.query(
        "select person_id, name, kana, gender from persons;",
         function(err, result) {
        res.write('<html><head></head><body><table>');
        for(lc = 0; lc < result.rows.length; lc++){
            res.write('<tr>');
            res.write("<td>"+result.rows[lc].person_id+"</td>" ); 
            res.write("<td>"+result.rows[lc].name+"</td>" ); 
            res.write("<td>"+result.rows[lc].kana+"</td>" ); 
            res.write("<td>"+result.rows[lc].gender+"</td>" ); 
            res.write('</tr>');
        }
        res.end('</table></body></html>')
    });
}).listen(8989)

プログラムとしては簡単です。
PostgreSQLに接続してクエリを実行し、実行結果をTABLEタグに出力するのみです。
nodeコマンドでNode.jsのサーバを起動します。


node pgConnect.js

URLにアクセスすると実行結果が表示されます。

http://localhost:8989/pgConnect

Node.jsの実行結果

まとめ


いかがでしょうか?
PostgreSQLであっても、Node.jsから簡単に接続がおこなうことができました。

本当はMongoDBとか使ってみたいんですが、しばらくはPostgreSQLでNode.jsの勉強を進めようと思います。