神出鬼没!サブクエリについて(Part.2)
はじめに
「サブクエリ」について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 );
社員テーブルの部署コードが、部署テーブルの有効フラグ = 1(有効) となっている部署コードと同じデータが抽出されていることを確認できます。IN句でサブクエリの結果に存在チェックを行い、存在する場合に抽出対象となっています。