神出鬼没！サブクエリについて(Part.2)

データベース

技術解説

連載 [第9回] :

初心者のための SQL-BOOTCAMP

2023年6月29日(木)

久保司

第9回となる今回は、前回に引き続き、SQL文の中でも神出鬼没でさまざまな出現パターンを持つサブクエリについて解説していきます。

はじめに

「サブクエリ」についてPart.2の今回は、FROM句および、WHERE句での使い方について解説していきます。サブクエリも習得してしまえば、BOOTCAMPの卒業まであと一息です。初回からの訓練で、戦闘力はかなりアップしたのではないでしょうか。

今回も、訓練に使用するデータベースは、これまでに引き続き、いつもの「SQLite」を使用していきます。

それでは、今回もエネルギッシュに訓練へ行ってみましょう!

FROM句で使用するケース

さっそく、FROM句でサブクエリを使用する例を確認してみましょう。FROM句では、サブクエリの結果を1つのテーブル(仮想のテーブル)として扱うことができます。

確認のために、サンプルデータと同様の社員テーブル(tbl_employees)を用意します。CREATEコマンドとINSERTコマンドは以下のようになります。

【社員テーブルCREATEコマンド】
※前回までに社員テーブルを作成している場合は、DROPコマンドで削除してから作り直しましょう。

DROP TABLE tbl_employees; 

CREATE TABLE tbl_employees (emp_code INTEGER , emp_name TEXT , kana TEXT , gender TEXT , age INTEGER , dept_code INTEGER);

【社員テーブルINSERTコマンド】

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');

作成した社員テーブルの内容を確認してみましょう。

.headers on
.mode column

SELECT *  FROM tbl_employees;

*「.headers」「.mode」は表示内容の設定コマンドなので、ログイン後に1回実行すればOKです。

INSERTコマンドで作成した、6件のデータが表示されています。

それでは、使用例のSELECT文を確認してみましょう。

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

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

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

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

ここで、仮想テーブルという言葉が出てきたので、サブクエリと同様に使用される「VIEW」(ビュー)を紹介します。

ビューは元のテーブルから必要なデータを抜粋や加工して取得し、仮想テーブルとして使用できます。また、どのようにデータを取得するかを定義しているのみなので、データ自体は保持していません。そのため、ビューにINSERT、UPDATE、DELETEはできないので注意してください。

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

【社員ビューCREATEコマンド】

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

上図のコマンドでは、社員テーブルの年齢が40以上のデータから、氏名、年齢の2項目のみ取得するよう定義しています。先ほどのサブクエリと同様の内容となります。

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

SELECT emp_name , age
  FROM emp_view;

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

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

SELECT emp_name,age
  FROM emp_view
WHERE age >= 42;

年齢が40以上で絞り込んだビューを、さらに42以上で絞り込んで表示しています。

WHERE句で使用するケース

データの存在チェックでよく利用されるWHERE句で使用する例を確認してみましょう。

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

【部署テーブルCREATEコマンド】
※前回までに部署テーブルを作成している場合は、DROPコマンドで削除してから作り直しましょう。

DROP TABLE tbl_dept; 

CREATE TABLE tbl_dept (dept_code INTEGER , dept_name TEXT , valid_flg INTEGER , update_user_id TEXT);

【部署テーブルINSERTコマンド】

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 *  FROM tbl_dept;

INSERTコマンドで作成した、4件のデータが確認できます。

使用例は、社員テーブルの部署コードが、部署テーブルの有効フラグ = 1(有効) の部署コードと同じ社員テーブルのデータを抽出していますので、結果を確認してみましょう。

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 );