【SQLレシピ】LEFT JOIN先にデータが存在しないレコードと存在するレコードをそれぞれカウントする

SQLでLEFT JOIN先にデータが存在しないレコードと存在するレコードをそれぞれカウントしたい！

概要

この記事では、SQLでLEFT JOIN先にデータが存在しないレコードと存在するレコードをそれぞれカウントする手順を掲載する。

仕様書

環境

SQLite 3.35.5
SQL Server 15.0.2000

手順書

下表のような人事情報を記録してるテーブルEmployeeを例にする。（サンプルなので正規化とか列名が不適切とかジョバンニが東京勤務とかは無視して下さい）

列名	データ型	備考
ID	INTEGER	ユニークな連番
Name	TEXT	氏名
Department	TEXT	部署
Localtion	TEXT	勤務地

登録してるデータは下記のとおり。

ID	Name	Department	Localtion
1	ジョバンニ	総務部	東京
2	カムパネルラ	営業部	東京
3	ザネリ	開発部	大阪
4	マルソ	製造部	仙台

今回は、LEFT JOINするので、もうひとつテーブルTOEICを用意した。このテーブルには社員のTOEICの点数が記録される。TOEICを受けてない社員のレコードは無い。

列名	データ型	備考
ID	INTEGER	ユニークな連番
EmployeeID	INTEGER	`Employee`のID
Score	INTEGER	点数

登録してるデータは下記のとおり。

ID	EmployeeID	Score
1	1	625
2	2	874
3	4	303

今回は、点数であるScoreを連結できたレコードの数と連結できなかったレコードの数をカウントするクエリを紹介する。

SELECT
    SUM(T.CNT_NULL) as SUM_NULL,
    SUM(T.CNT_NOT_NULL) as SUM_NOT_NULL
FROM (
    SELECT
        CASE WHEN TOEIC.Score IS NULL THEN 1 ELSE 0 END AS CNT_NULL,
        CASE WHEN TOEIC.Score IS NOT NULL THEN 1 ELSE 0 END AS CNT_NOT_NULL
    FROM EMPLOYEE
    LEFT JOIN TOEIC ON TOEIC.EmployeeID = EMPLOYEE.ID) T

クエリを実行すると下記のようにTOEICを受てない社員の数の列SUM_NULL列と受けた社員の数のSUM_NOT_NULLが出力される。

SUM_NULL	SUM_NOT_NULL
1	3

まとめ（感想文）

値によってレコードを分類したい場合とかに使えるかもね！