実践して理解するSQL ~CASE文, HAVING句~

はじめに

本記事はSQLについて、SQLの基本的なselect, create, update, deleteを学習後、
少し発展系を勉強したいといった方を対象にした記事になっています。
本記事で取り上げるのは CASE文,HAVING句になっています。

実行環境

本記事で使用したツールは以下のようになっています。

■SQLクライアント
- DBeaver
 * バージョン7.1.1.202006231904

確認方法

mysql公式のサンプルデータベースを以下からダウンロードすることができるので便利、
これがあればSQLを学習するときに面倒なテストデータを作成することが省けるのでかなり楽。
http://downloads.mysql.com/docs/world.sql.gz

ダウンロードしたはいいが使い方がよくわからないって方はこちらを参考にするといいかもしれない。

ER図

ダウンロードしてきたDB情報は以下のようになっている
world_owned_media_resource.png

CASE文

CASE文とは、プログラミングをしている方であれば、していない方でも文字を見れば何かの条件分岐に使うんだろうなぁって予想がつくと思います。
「〇〇であれば〜〜」という場合分けをしたい時に使用する構文です。

■構文確認
CASE 対象範囲 WHEN 条件 THEN 条件が一致する場合 ELSE 条件が何にも一致しない場合 END

CASE city
    WHEN '北海道' THEN 1
    WHEN '沖縄' THEN 2
    ELSE 99
END

// または
CASE 
    WHEN city = '北海道' THEN 1
    WHEN city = '沖縄' THEN 2
    ELSE 99
END

■実例確認
ダウンロードデータがある場合には以下のSQlを実行してもらえれば動きます。

やることはシンプル
worldデータベースのcityテーブルからNameカラムの値を取得する、 CountryCodeがJPNであればJAPAN、NLDであればNETHERLANDS、それ以外であればotherと出力する

SELECT 
 Name, 
 CASE 
    WHEN CountryCode = "JPN" THEN "JAPAN" 
    WHEN CountryCode ="NLD" THEN "NETHERLANDS"
    ELSE "other"
    END
 FROM world.city
 WHERE CountryCode = "JPN";

//または
//CASEのあとに検索先を指定することで、whenの後のCountryCodeを省略することもできる

SELECT 
 Name, 
 CASE CountryCode 
    WHEN "JPN" THEN "JAPAN" 
    WHEN "NLD" THEN "NETHERLANDS"
    ELSE "other"
 END
FROM world.city 
WHERE CountryCode = "JPN" OR CountryCode = "NLD";

■実行結果
日本の都市名が表示されていればJAPAN、オランダの都市名が表示されて入ればNETHERLANDSと表示されていることが確認できる。
スクリーンショット 2020-09-06 14.37.56.png
スクリーンショット 2020-09-06 14.38.14.png

HAVING句

HAVINGとは抽出条件を指定を指定するものです。
抽出条件を指定をするものと聞いて、疑問に思うのが、HAVINGと同様に抽出条件を指定するものとしてのWHERE句の存在です。
HAVING句WHERE句の違いについて確認する前に、構文の確認だけしたいと思います。
■構文確認

構文の確認、とってもシンプルでHAVING 抽出条件と記述するだけ。

HAVING CountryCode = "JPN"

■HAVING句とWHERE句の違い

結論から行ってしまうと、呼び出される順番が違います。
FROM → WHERE → GROUPBY → HAVING → SELECT → ORDERBY
ここでポイントとして確認したいのが、それぞれGROUPBYの前後にあるということです。

  • WHERE句は、GROUPBYでグルーピングするに条件を指定して抽出する
  • HAVING句は、GROUPBYでグルーピングするに条件を指定して抽出する
  • 抽出する値や、グルーピングした値に対して、に条件を指定して抽出する

これだけ言われてもよくわからないので、実際SQLを確認しながら動きのイメージを掴んでいければと思います。

■実例確認

WHERE句を使って抽出

  • WHERE句は、GROUPBYでグルーピングするに条件を指定して抽出する

まずは、group byを宣言する前に、WHERE句を宣言して実行。

SELECT
  IsOfficial
FROM 
  world.countrylanguage
WHERE 
  IsOfficial = "T";

下記のように、問題なくSQLが実行され、
worldデータベースのcountrylanguageテーブルのIsOfficialカラムがTの値を抽出する

group by を追加し、countも追加してSQLを実行する。

SELECT
  IsOfficial,
  COUNT(IsOfficial) as official_language_number 
FROM 
   world.countrylanguage c 
where 
  IsOfficial = "T"
group by 
  IsOfficial; 

下記のように、問題がなくSQLが実行され
worldデータベースのcountrylanguageテーブルのIsOfficialカラムがTの値をグルーピング化し、IsOfficialがTの値を抽出し、Tがいくつあるのかをcountして表示することできた。こちらがWHERE句のイメージでgroup byの前に WHERE句が呼ばれる。

■HAVING句を使って抽出

  • HAVING句は、GROUPBYでグルーピングしたに条件を指定して抽出する

まずはHAVING句がなく、GROUPBYがある状態

SELECT
  IsOfficial,
  COUNT(IsOfficial) as official_language_number 
FROM 
   world.countrylanguage c 
group by 
  IsOfficial; 

IsOfficialの値ごとにGROUPBYされ、IsOfficialの値がcountされて表示された。

次にHAVING句で条件を指定してSQLを実行する。

SELECT
  IsOfficial,
  COUNT(IsOfficial) as official_language_number 
FROM 
   world.countrylanguage c 
group by 
  IsOfficial
HAVING IsOfficial = "T";

SQLが実行され、IsOfficial がT のものだけを抽出しcountして表示することができた。

■抽出する値や、グルーピングした値に対して、に条件を指定する

WHERE句を使って抽出

SELECT
  COUNT(IsOfficial) as official_language_number 
FROM 
   world.countrylanguage c 
WHERE
  IsOfficial = "T"

下記のように、問題がなくSQLが実行され
worldデータベースのcountrylanguageテーブルのIsOfficialカラムがTの条件の値を抽出し、Tがいくつあるのかをcountして表示することできた。

HAVING句を使って抽出

WHERE句、同様に条件の部分をHAVINGにして実行。

SELECT
  COUNT(IsOfficial) as official_language_number 
FROM 
   world.countrylanguage
HAVING 
  IsOfficial = "T";

抽出する対象のカラムがないため、
以下のような「having句内にはIsOfficial列はありません」といったエラーメッセージが出力される。

以下のようなSQLを実行する

SELECT
  IsOfficial, 
  COUNT(IsOfficial) as official_language_number 
FROM 
   world.countrylanguage c 
HAVING 
  IsOfficial = "T";

今度は以下のように、正常にSQLが実行される。

このことから以下のようなことわかる

  • WHERE句が直接テーブル操作する時の、条件に使用することができる
  • HAVING句はGROUPBYやカラムで抽出する対象のデータに対して条件を指定して、SQLを実行する。

終わりに

今回については、CASE文とHAVING句について確認しました。
SELECT, UPDATE, DELETE, CREATEを組み合わせて使えばより強力な武器になること間違いなしなので、
是非実戦で使ってもらえればと思います。

次回はより複雑なデータ構造を使って、必要なデータを抽出するようなことをやっていきたいと思います。