【SQL】サブクエリ入門

サブクエリとは、簡単にいうとクエリ内のクエリのこと。もしくはそれを利用したSQLのこと。
考え方は難しくはないが、サブクエリが使える場所は複数あるため、整理しておきたい。

サブクエリの一種である相関サブクエリについては別の記事に書いた。

動作確認環境とテーブル

PostgreSQL 9.4でクエリの動作を確認した。
また、使用したテーブルの定義は下記。

CREATE TABLE students (
  id   INTEGER PRIMARY KEY,
  name VARCHAR(10) NOT NULL,
  age  INTEGER
);

CREATE TABLE courses (
  id   INTEGER PRIMARY KEY,
  name VARCHAR(10) NOT NULL
);

CREATE TABLE enrollments (
  courseId  INTEGER REFERENCES courses(id),
  studentId INTEGER REFERENCES students(id),
  PRIMARY KEY(courseId, studentId)
);

データは適当にINSERTする。

INSERT INTO students VALUES(1,'Sato',14);
INSERT INTO students VALUES(2,'Suzuki', 15);
INSERT INTO students VALUES(3,'Yamada',12);
INSERT INTO students VALUES(4,'Tanaka',14);

INSERT INTO courses VALUES(1,'Math');
INSERT INTO courses VALUES(2,'Japanese');
INSERT INTO courses VALUES(3,'History');

INSERT INTO enrollments VALUES(1,1);
INSERT INTO enrollments VALUES(1,2);
INSERT INTO enrollments VALUES(3,1);
INSERT INTO enrollments VALUES(3,3);

SELECTするとこのようになっているはず。

# select * from students ;
 id |  name  | age 
----+--------+-----
  1 | Sato   |  14
  2 | Suzuki |  15
  3 | Yamada |  12
  4 | Tanaka |  14
(4 rows)



# select * from courses ;
 id |   name   
----+----------
  1 | Math
  2 | Japanese
  3 | History
(3 rows)


# select * from enrollments ;
 courseid | studentid 
----------+-----------
        1 |         1
        1 |         2
        3 |         1
        3 |         3
(4 rows)

いろいろなサブクエリ

FROM句で使う

使い捨てのビューを作成して使うイメージ。

下記のSQLは、生徒テーブルから「生徒の年齢」と「その年齢をもつ生徒の人数」を問い合わせる。

SELECT age, age_count
  FROM (SELECT age, COUNT(age) as age_count
          FROM students
         GROUP BY age) as s1;

 age | age_count 
-----+-----------
  15 |         1
  14 |         2
  12 |         1
(3 rows)

これは、下記のようなビューを考えるとわかりやすい。

CREATE VIEW s1 AS
    SELECT age, COUNT(age) as age_count
      FROM students
     GROUP BY age;

select age, age_count from s1;
 age | age_count 
-----+-----------
  15 |         1
  14 |         2
  12 |         1
(3 rows)

サブクエリ内で集約を用いているが、集約関数を使用した項目に対して絞りこみを行いたい場合はHAVINGではなくWHEREを使う。（ビューを使用した場合と同じ）

SELECT s1.age, s1.age_count
  FROM (SELECT age, COUNT(age) as age_count
          FROM students
         GROUP BY age) as s1
 WHERE age_count = 2;

 age | age_count 
-----+-----------
  14 |         2
(1 row)

WHERE句で使う

これはINといっしょに使われるケースが多い。

下記は、14歳の生徒が受講するコースをすべて表示するSQLである。
SQLでは重複する要素もそのまま重複した状態で出力されるため、"DISTINCT"を指定している。

SELECT DISTINCT courseId
  FROM enrollments
 WHERE studentId IN (SELECT id
                       FROM students
                      WHERE age = 14); 
 courseid 
----------
        1
        3
(2 rows)

これは結合を使って書き換えられる。

SELECT DISTINCT courseId
  FROM enrollments, students
 WHERE enrollments.studentId = students.id
   AND students.age = 14;

 courseid 
----------
        1
        3
(2 rows)

SELECT文の指定列の中で使う

まずは実行例を見る。
下記のSQLは、生徒テーブルの内容を生徒の平均年齢とともに表示する。

SELECT name,
       age,
       (SELECT AVG(age)
          FROM students)
  FROM students;

  name  | age |         avg         
--------+-----+---------------------
 Sato   |  14 | 13.7500000000000000
 Suzuki |  15 | 13.7500000000000000
 Yamada |  12 | 13.7500000000000000
 Tanaka |  14 | 13.7500000000000000
(4 rows)

ポイントは、このサブクエリは単一行を返すことが保証されたクエリでなければならない点だ。
もしも複数行を返しうるクエリがこの場所に書けてしまったら、そのうちどれを表示していいか不明だからだ。

このようなサブクエリを「スカラ・サブクエリ」と呼ぶらしい。（詳細はミック氏の『SQL ゼロからはじめるデータベース操作』を参照）

WHERE句において、INではなく不等号と一緒にサブクエリを使いたい場合も、スカラサブクエリであることが要求される。

多段のサブクエリ

ここまで見てきた１つのSQL文の中にサブクエリが１つ含まれるシンプルなものだったが、サブクエリを重ねて多段にすることもできる。

ただし可読性は低く、使用するケースも多くはないのでここでは割愛する。