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

ネコニウム研究所

PCを利用したモノづくりに関連する情報を掲載するブログ

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

2022-6-2 |

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

まとめ(感想文)

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