with句

By clear 9月 23, 2020

注意

本記事は、SELECT文 (SQL) の応用的な内容について記載しています。
そのため、下記を前提とした内容となっています。

  • SELECT文の基本的な文法を知っている
  • 副問合せを知っている

初めに

今回は、データ抽出にて用いられるwith句について説明します。
with句のメリットは、(with句を使わない)副問合せとの比較することで実感できると思います。
なので、上記の[0.注意]の通りで記事を書いています。
副問合せをよく知らない方は、本ブログの別記事にて説明してるので、事前に目を通していただければと思います。

「with句」とは

簡単に言うと「副問合せをエイリアスで定義する書き方」です。
イメージとしては、「一般のプログラミングにおける変数定義」のようなものです。
下記コードのような枠組みで書くことができます。
with句は副問合せの記法の1つになります。
with句を使うメリットとしては、下記2点です。

  • 1つの問合せの中で、1回(with句で)指定をすれば、その問合せ内でなら色々な箇所でエイリアス指定が可能
  • 副問合せ(内枠)と主の問合せ(外枠)をコード的に分けて書くため、コードが読みやすい。
with sub_1 as (
    select 
    from table_1
    where 
), sub_2 as (
    select 
    from table_2
        inner join sub_1
        on  
)
select 
from sub_1 
    inner join sub_2
    on 
;

「with句」の例 (シンプルな抽出)

下記データに対して、“営業部の社員名を抽出”するように上司から求められたとします。

[データ]

部署ID部署名部長ID
1人事4
2総務2
3営業5
部署マスタ
社員ID社員名部署ID
1田中2
2佐藤2
3高橋3
4飯田1
5吉田3
社員マスタ

[求める抽出結果]

社員名
高橋
吉田
営業部の社員名

このデータ抽出を、”with句を使わない場合”と”with句を使う場合“それぞれを比較してみましょう。

例.1 with句を使わない場合

select t1.社員名
from 社員マスタ t1 
    inner join (
        select 部署ID
        from 部署マスタ
        where 部署名 = '営業'
    ) t2
    on t1.部署ID = t2.部署ID
;

例2. with句を使う場合

with sales_dep as (
    select 部署ID
    from 部署マスタ
    where 部署名 = '営業
)
select t1.社員名
from 社員マスタ t1 
    inner join sales_dep t2
    on t1.部署ID = t2.部署ID
;

with句を使用した方が、副問合せのみを先に定義しているので、コードが読みやすくなってると思います。
ただ、本例題のレベルだと、あまり違いが感じられない方もいるかもしれません。
少し複雑度を上げた例も見てみましょう。

「with句」の例 (複雑な抽出)

下記データに対して、「“社員が2名以上いる部署”の部長の社員名と所属部署名」を抽出するように求められたとします。

[データ]

部署ID部署名部長ID
1人事4
2総務2
3営業5
部署マスタ
社員ID社員名部署ID
1田中2
2佐藤2
3高橋3
4飯田1
5吉田3
社員マスタ

[求める抽出結果]

社員名部署名
佐藤総務
吉田営業
社員2名以上の部署の部長データ

このデータ抽出を、”with句を使わない場合”と”with句を使う場合“それぞれを比較してみましょう。

例.1 with句を使わない場合

select t1.社員名 , t2.部署名
from 社員マスタ t1 
    inner join (
        /* 部長ID (但し、2名以上の社員が所属する部署のみ) */
        select t3.*
        from 部署マスタ t3
            inner join (
                /* 2名以上の社員が所属する部署ID */
                select 部署ID , count(*) as cnt_employee
                from 部署マスタ
                group by 部署ID
                having cnt_employee >= 2
            ) t4
            on t3.部署ID = t4.部署ID
    ) t2
    on t1.部署ID = t2.部署ID
;

例2. with句を使う場合

/* 2名以上の社員が所属する部署ID */
with dep_id_2_emp as (
    select 部署ID , count(*) as cnt_employee
    from 部署マスタ
    group by 部署ID
    having cnt_employee >= 2
)
/* 部長ID (但し、2名以上の社員が所属する部署のみ) */
, directors_2_emp_dep as (
    select t1.*
    from 部署マスタ t1
        inner join dep_id_2_emp t2
        on t1.部署ID = t2.部署ID
)
select t1.社員名 , t2.部署名
from 社員マスタ t1 
    inner join directors_2_emp_dep t2
    on t1.部署ID = t2.部署ID
;

両方の例は、同じ副問合せを用いて書いています。
このぐらい複雑な抽出であれば、with句の方が可読性の高さが際立つと思います。

最後に

副問合せを行う際にwith句を使うメリットに着目して説明しました。
筆者の経験上、業務において、”多少でも複雑な抽出の場合”は、必ずと言っていい程with句が使われいる印象があります。
業務でSQLを使用する際は、with句を活用できるようにしましょう。

By clear

データエンジニア・機械学習・分析等を主とし、Webアプリ開発も行っているフリーランスです。

One thought on “with句”
  1. with句の説明とても分かりやすく、実務上の使用方法など記載があり、良かったです(^^)。
    あまりwith句を使ったことないのですが、
    副問合せをする際は意識してみます。

Comments are closed.