SQLのサブクエリを覚えよう！（FROM句、WHERE句での使い方）

2023.08.17 株式会社システムインテグレータ

今回の初心者訓練所#10は、「サブクエリ」を覚えようPart.2として、FROM句および、WHERE句での使い方について説明していきます。前回と合わせて「サブクエリ」を習得してしまえば、初心者の域も超えてしまうかもですね。今回もデータベースは、これまでに引き続き、いつもの「SQLite」を使用していきます。

FROM句：サブクエリの値をテーブルとして使用するケース

さっそく、FROM句でサブクエリを使用するケースを確認してみましょう。FROM句では、サブクエリの結果を１つのテーブル（仮想のテーブル）として扱うことができます。
確認のために、社員テーブル(tbl_employees)を用意します。create文とinsert文は以下となります。

【社員テーブルのcreate文とinsert文】

create table tbl_employees (emp_code integer , emp_name text , kana text , gender text , age integer , dept_code integer);

insert into tbl_employees (emp_code,emp_name,kana,gender,age,dept_code) values
('1','HoriiTaro','ﾎﾘｲﾀﾛｳ','M','40','1')
, ('2','IshimuraHana','ｲｼﾑﾗﾊﾅ','F','33','2')
, ('3','KitajimaRuna','ｷﾀｼﾞﾏﾙﾅ','F','45','3')
, ('4','MuroiYuri', 'ﾑﾛｲﾕﾘ','F', '27','4')
, ('5','KitazawaAika','ｷﾀｻﾞﾜｱｲｶ','F','33','4')
, ('6','MitaniKoukiti','ﾐﾀﾆｺｳｷﾁ','M','23','2');

sql10-01

出力フォーマットを調整して、select文で登録内容を確認してみましょう。

.headers on　（ヘッダ情報を出力）
.mode column　（カラム毎に左揃え出力）

select * from tbl_employees;

sql10-02

insert文で作成した6件のデータを確認できます。

それでは、FROM句でサブクエリを使用したSELECT文を確認してみましょう。

select sub1.emp_name , sub1.age
from (SELECT emp_name , age
FROM tbl_employees
WHERE age >= 40) AS sub1;

sql10-03

社員テーブルの年齢が40以上のデータを取得していることが確認できます。

サブクエリで、予めデータを絞り込んだ仮想テーブルを作成し、その仮想テーブルからデータを取得しているイメージになります。上記では、説明のためにあえてサブクエリを使用しましたが、もちろん、下記のようにメインのクエリにWHERE句を記述したSELECT文でも同じデータを取得できます。

select emp_name,age from tbl_employees where age >= 40;

sql10-04

仮想テーブル「VIEW」について

ここで、仮想テーブルという言葉が出てきましたので、サブクエリと同様に使用される「VIEW」（ビュー）を紹介します。ビューは、元のテーブルから必要なデータを抜粋や加工して取得し、仮想テーブルとして使用できます。また、どのようにデータを取得するかを定義しているのみなので、データ自体は保持していません。そのため、ビューに対してINSERT、UPDATE、DELETEは出来ないので注意してください。

ビューもテーブルと同様にコマンドで作成できます。

【社員ビューcreate文】

CREATE VIEW emp_view AS
SELECT emp_name,age FROM tbl_employees WHERE age >= 40;

sql10-05

上図のビューのcreate文では、テーブルのcreate文とは違い、先ほどのサブクエリと同様の内容となる、社員テーブルの年齢が40以上のデータから、氏名、年齢の2項目のみ取得するSELECT文を定義しています。

では、ビューの内容を確認してみましょう。

select * from emp_view;

sql10-06

結果はサブクエリの場合と同じになりますが、記述するSELECT文は簡略化されています。このように、ビューは「age >= 40」の様に、同じ条件のデータを使いまわす場合はとても便利ですが、条件が都度変更になる場合等は、サブクエリの利用を検討しましょう。

ビューは、テーブルと同様に扱えますのでもちろんWHERE句も使用できます。

select * from emp_view where age >= 42 ;

sql10-07

WHERE句：サブクエリを存在チェックに使用するケース

サブクエリ最後の説明になりますが、データの存在チェックでよく利用されるWHERE句で使用するケースを確認してみましょう。

確認の準備として、部署テーブル(tbl_dept)を作成して、データを挿入します。

【部署テーブルのcreate文とinsert文】

create table tbl_dept (dept_code integer , dept_name text, valid_flg integer , update_user_id text);

insert into tbl_dept (dept_code , dept_name , valid_flg , update_user_id ) values
('1','技術部','1','YAMADA'), ('2','人事部','1','YAMADA')
, ('3','総務部','1','TANAKA'), ('4','経理部','0','SATO');

select文で登録内容を確認してみましょう。

select * from tbl_dept;

sql10-09

insert文で作成した4件のデータを確認できます。

それでは、WHERE句でサブクエリを利用して存在チェックをしているSELECT文を確認してみましょう。

select emp.emp_name , emp.dept_code
from tbl_employees as emp
where emp.dept_code IN ( SELECT dp.dept_code
　 FROM tbl_dept AS dp
WHERE dp.valid_flg = 1 );

sql10-10

社員テーブルの部署コードが、部署テーブルの有効フラグ＝ 1（有効）となっている部署コードと同じデータが抽出されていることを確認できます。IN句でサブクエリの結果に存在チェックを行い、存在する場合に抽出対象となっています。

（１）IN句で存在チェック
　IN句は、複数の値のいずれかと一致するとデータを取得します。指定した値との比較や、前述のように、サブクエリで取得した値との比較が出来ます。また、「NOT IN」のようにNOTを前に付加すると、指定した値以外のデータを取得してくれます。

sql10-11

[使用例1]の、値を直接指定した場合についてそれぞれ確認してみましょう。

select emp_name , dept_code
from tbl_employees
where dept_code IN ( 2 , 3 );

sql10-12

部署コードが2または３のデータが抽出されています。
WHERE句を「dept_code = 2 OR dept_code = 3」と記述した場合と同じ結果ですが、IN句を使用するとシンプルに記述できます。

「NOT IN」も確認してみましょう。

select emp_name , dept_code
from tbl_employees
where dept_code NOT IN ( 2 , 3 );

sql10-13

NOT INでは、部署コードが2、３以外のデータが抽出されているのが確認できます。

sql10-14

[使用例2]は、WHERE句の初めの説明で紹介したクエリになりますが、サブクエリでテーブルから取得した値と比較しています。

サブクエリで取得される値を、直接指定すると以下のクエリになります。

select emp.emp_name,emp.dept_code
from tbl_employees as emp
where emp.dept_code IN ( 1 , 2 , 3 );

sql10-15

結果は、もちろんサブクエリを使用した場合と同じになります。

（２）EXISTS句で存在チェック
　ここでIN句と同様に、存在チェックによく利用されるEXISTS句を紹介します。EXISTS句は、サブクエリで指定した条件のデータが存在するかどうかをチェックします。存在する場合は「TRUE」、存在しない場合は「FALSE」を返します。「TRUE」となった場合に、該当するメインテーブルのデータが抽出されます。

sql10-16

確認の準備として、部署テーブルから部署コード = 4のデータを削除します。削除後の結果も確認してみましょう。

delete from tbl_dept where dept_code = 4;

select * from tbl_dept;

sql10-17

部署コード = 4のデータが削除されていることが確認できます。

それでは[使用例]のSELECT文を確認してみましょう。サブクエリで社員テーブルの部署コードと部署テーブルの部署コードを比較して、存在する場合は「TRUE」、存在しない場合は「FALSE」が返されます。

select emp.emp_name , emp.dept_code
from tbl_employees as emp
where EXISTS ( SELECT dp.dept_code
FROM tbl_dept AS dp
WHERE dp.dept_code = emp.dept_code );

sql10-18

先ほど部署テーブルから削除した部署コード= 4の社員テーブルのデータは、EXISTS句でのチェック結果が「FALSE」になるため表示されていないことが確認できます。

EXISTS句にも「NOT」を使用することができます。

select emp.emp_name,emp.dept_code
from tbl_employees as emp
where NOT EXISTS ( SELECT dp.dept_code
FROM tbl_dept AS dp
WHERE dp.dept_code = emp.dept_code );

sql10-19

NOTを付加すると、部署テーブルに存在しない部署コード= 4のデータが表示されます。

参考までに、EXISTS句を内部結合で書き換えた場合と、NOT EXISTS句を外部結合で書き換えた場合を紹介します。
【EXISTSを内部結合で書き換えた場合】

select emp.emp_name,emp.dept_code
from tbl_employees as emp
inner join tbl_dept as dp on dp.dept_code = emp.dept_code;

sql10-20

部署コードで内部結合しているので、部署コードが両方のテーブルに存在するデータが表示されます。

【NOT EXISTSを外部結合で書き換えた場合】

select emp.emp_name,emp.dept_code
from tbl_employees as emp
left outer join tbl_dept as dp on dp.dept_code = emp.dept_code
where dp.dept_code IS NULL;

sql10-21

外部結合先の部署テーブルに部署コードが存在しない場合、部署コードがNULLとなるので、WHERE句でIS NULLの条件を記述しています。

サブクエリの説明は以上となります。

まとめ

初心者訓練所の第10弾となる今回は、FROM句とWHERE句で使用するサブクエリについて説明しました。前回から2回に渡り説明してきましたが、使い方のイメージはつかめたでしょうか。最後のEXISTS句でも紹介したように、SQLは様々な記述方法がありますので、是非、トライ＆エラーを繰り返しながらスキルアップに励んでみてください。

(株)システムインテグレータ　インキュベーション部　元鬼軍曹　久保　司