注意
本記事は、SELECT文 (SQL) の応用的な内容について記載しています。
そのため、下記を前提とした内容となっています。
- SELECT文の基本的な文法を知っている
- 副問合せを知っている
初めに
今回は、データ抽出にて用いられるwith句について説明します。
with句のメリットは、(with句を使わない)副問合せとの比較することで実感できると思います。
なので、上記の[0.注意]の通りで記事を書いています。
副問合せをよく知らない方は、本ブログの別記事にて説明してるので、事前に目を通していただければと思います。
「with句」とは
簡単に言うと「副問合せをエイリアスで定義する書き方」です。
イメージとしては、「一般のプログラミングにおける変数定義」のようなものです。
下記コードのような枠組みで書くことができます。
with句は副問合せの記法の1つになります。
with句を使うメリットとしては、下記２点です。
- 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 |
[求める抽出結果]
| 社員名 | 部署名 | |
| 佐藤 | 総務 | |
| 吉田 | 営業 |
このデータ抽出を、”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句を活用できるようにしましょう。
with句の説明とても分かりやすく、実務上の使用方法など記載があり、良かったです(^^)。
あまりwith句を使ったことないのですが、
副問合せをする際は意識してみます。