SQLのサブクエリを覚えよう!(FROM句、WHERE句での使い方)

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

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

教養としてのプログラミング入門BOOK

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

さっそく、FROM句でサブクエリを使用するケースを確認してみましょう。FROM句では、サブクエリの結果を1つのテーブル(仮想のテーブル)として扱うことができます。
確認のために、社員テーブル(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');
  sql10-08

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句でサブクエリの結果に存在チェックを行い、存在する場合に抽出対象となっています。

(1)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または3のデータが抽出されています。
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、3以外のデータが抽出されているのが確認できます。

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

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

(2)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の条件を記述しています。

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

おすすめの学習コンテンツ

本記事を読み終わって、さらに学びたい方、次は問題にチャレンジしてみたい方には、以下の無料コンテンツをご用意しておりますので、ぜひ活用してみてください!

SQLiteについて
・本記事で使用したデータベース「SQLite」について解説する資料です。
 SQLiteとは?他のデータベースとの違いも解説

SQLの教科書
・一般的な解説から、実際にSQLを動かす操作イメージまで幅広く解説する資料です。
 「SQLの教科書」ビックデータ時代の必須科目

SQL無料トライアルテスト
・TOPSIC-SQLの無料トライアルテストをご受験していただけます。
 TOPSIC-SQL無料トライアルテスト

SQLコンテスト
・簡単な会員登録をするだけでSQLのオンラインコンテストに無料で参加できます。
    過去に開催されたコンテストの問題にもチャレンジできます。
 TOPSIC SQL CONTEST

SQL基礎解説YouTube
・「TOPSIC-SQL」を元にしたオンラインウェビナーです。
 SQL-BOOT CAMP #1 
 ビッグデータ時代の必須科目SQLを学ぶ「実戦で通用する武器を身につけよう!」

まとめ

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

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

ビッグデータ時代の必須科目 SQLの教科書(初級編)

RELATED POST関連記事


RECENT POST「SQL‐BOOTCAMP」の最新記事


SQL‐BOOTCAMP

SQLのウィンドウ関数(PERCENT_RANK、CUME_DIST、NTILE)を使ってみよう!

SQL‐BOOTCAMP

SQLのウィンドウ関数(ROW_NUMBER、RANK、DENSE_RANK)を使ってみよう!

SQL‐BOOTCAMP

SQLのテーブル結合(JOIN, UNION, NATURAL)を覚えよう!基本構文と使用例を解説

SQL‐BOOTCAMP

SQLのウィンドウ関数(OVER句でPARTITION BY、ROWS、RANGE)を使ってみよう!

SQLのサブクエリを覚えよう!(FROM句、WHERE句での使い方)

TOPSIC TOPへ

TOPSIC-SQL4コマ漫画

RANKING人気資料ランキング

RANKING人気記事ランキング

RECENT POST 最新記事