Help us understand the problem. What is going on with this article?

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

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

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

サブクエリが何かわからない場合は、こちらを先に読んで欲しい。
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句で指定されたサブクエリは、外側のクエリの結果の1行ごとに実行され、その外側のクエリの結果の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)

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

aki3061
ソフトウェアエンジニア
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした