【SQL】相関サブクエリ入門

相関サブクエリとはサブクエリの一種であり、外側のクエリの値をサブクエリ内で使用する。

相関サブクエリを使用したSQLはややこしくて読みにくい場合が多いが、基本形を１つおさえておくとだいぶ理解しやすくなる。

サブクエリが何かわからない場合は、こちらを先に読んで欲しい。
http://qiita.com/mokrai/items/6df0513ccc5aa40a075a

動作確認環境とテーブル

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

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

INSERT INTO Employees VALUES(1,'Sato',23,'営業');
INSERT INTO Employees VALUES(2,'Suzuki',35,'営業');
INSERT INTO Employees VALUES(3,'Saito',38,'営業');
INSERT INTO Employees VALUES(4,'Yamada',42,'開発');
INSERT INTO Employees VALUES(5,'Tanaka',41,'開発');
INSERT INTO Employees VALUES(6,'Takahashi',35,'開発');

SELECTするとこんな感じ。

# SELECT * FROM Employees ;

 id |   name    | age | department 
----+-----------+-----+------------
  1 | Sato      |  23 | 営業
  2 | Suzuki    |  35 | 営業
  3 | Saito     |  38 | 営業
  4 | Yamada    |  42 | 開発
  5 | Tanaka    |  41 | 開発
  6 | Takahashi |  35 | 開発
(6 rows)

実行例

下記のSQLは、所属部署の平均年齢よりも若い社員を表示する。(ついでに年齢と所属部署も表示する)
ここで「所属部署の」というのがポイントであり、たとえば「営業」の社員であれば営業に所属する社員の平均年齢が基準となる。（つまり、開発社員の年齢は考慮しない）

SELECT name, age, department
  FROM Employees as e1
 WHERE e1.age < (SELECT AVG(age) as avg_age
                   FROM Employees as e2
                  WHERE e1.department = e2.department);

   name    | age | department 
-----------+-----+------------
 Sato      |  23 | 営業
 Takahashi |  35 | 開発
(2 rows)

このSQLで注目すべきは、WHERE e1.department = e2.departmentの部分だ。
WHERE句で指定されたサブクエリは、外側のクエリの結果の１行ごとに実行され、その外側のクエリの結果のdepartmentの値をサブクエリ内で利用している。

「所属部署の」という制限を外す場合、つまり「社員全体の平均年齢よりも若い社員」を知りたい場合は、相関サブクエリではないサブクエリを用いる。

SELECT name, age, department
  FROM Employees
 WHERE age < (SELECT AVG(age)
               FROM Employees);

   name    | age | department 
-----------+-----+------------
 Sato      |  23 | 営業
 Suzuki    |  35 | 営業
 Takahashi |  35 | 開発
(3 rows)

所属部署の平均年齢もあわせて表示したい場合

SELECTの列の指定で、WHERE句と同じ式を書けば実現できる。
（DRY原則に反しているので、もっとスマートなやり方があれば教えてください。）

SELECT name,
       age,
       department,
       (SELECT AVG(age) as avg_age
                   FROM Employees as e2
                  WHERE e1.department = e2.department)
  FROM Employees as e1
 WHERE e1.age < (SELECT AVG(age) as avg_age
                   FROM Employees as e2
                  WHERE e1.department = e2.department);

   name    | age | department |       avg_age       
-----------+-----+------------+---------------------
 Sato      |  23 | 営業       | 32.0000000000000000
 Takahashi |  35 | 開発       | 39.3333333333333333
(2 rows)

２つのサブクエリ内でe2という同一のテーブルの別名を指定している点が気になったかもしれないが、サブクエリ内でスコープが分かれるため問題ない。