相関サブクエリの使い方 - koheitakahashiのブログ

サブクエリとは?

使い捨てのビューのことです。ビューとは、自分が頻繁に使うようなSELECT文をデータベースに登録しておく機能のことを言います。

相関サブクエリとは?

例えば、以下のようなデータベースがあるとします。

CREATE TABLE Customer
( id        CHAR(4)       NOT NULL,
  name      VARCHAR(100)  NOT NULL,
  address    VARCHAR(100)  NOT NULL,
  sex       VARCHAR(3)    NOT NULL,
  age       INTEGER       ,
  torokubi  DATE       ,
  PRIMARY KEY (id));

BEGIN TRANSACTION;

INSERT INTO Customer VALUES ('0001','深沢諭吉', '東京都', '男性', '35', '2001-08-06');
INSERT INTO Customer VALUES ('0002','北口英世', '静岡県', '男性', '24', '2001-11-11');
INSERT INTO Customer VALUES ('0003','樋口二葉', '東京都', '女性', '42', '2002-02-24');
INSERT INTO Customer VALUES ('0004','天沢栄一', '静岡県', '男性', '16', '2002-04-16');
INSERT INTO Customer VALUES ('0005','津田桃子', '東京都', '女性', '56', '2003-05-22');
INSERT INTO Customer VALUES ('0006','南里柴三郎', '静岡県', '男性', '80', '2004-12-01');

COMMIT;

ここで、「県別の平均年齢よりも高い年齢の顧客の名前をピックアップした」という状況になったとき、さっきのサブクエリを使うと実現できるかもしれません。

やってみましょう。

1. まず、県別の年齢の平均を出してみる

コード1

SELECT AVG(age), address
  FROM Customer 
  GROUP BY address;

結果1

         avg         | address 
---------------------+---------
 40.0000000000000000 | 静岡県
 44.3333333333333333 | 東京都
(2 rows)

2. 1のSELECT文をそのままWHERE句に入れる

コード2

SELECT name, age, address
FROM Customer
WHERE age > (SELECT AVG(age)
            FROM Customer 
            GROUP BY address);

これで、うまくいくはず・・・

結果2

ERROR:  more than one row returned by a subquery used as an expression

あれ、エラーが起きた😱

エラーの原因は？

実は、サブクエリをWHERE句の中に記述するためには、サブクエリが返す値が1つでなくてはなりません。このように、返す値が1つという条件を持ったサブクエリをスカラ・サブクエリと言います。

今回は、サブクエリ部分が東京都と静岡県の2つの平均年齢を返したため、このようなエラーが出てしまいました。

ではどうすればいいのでしょう?

ここで相関サブクエリが登場

実はコード2に1文だけ加えれば上記の課題が解決できるのです。

それがWHERE句です。

コード3

SELECT name, age, address
FROM Customer AS C1
WHERE age > (SELECT AVG(age)
            FROM Customer AS C2
            WHERE C1.address = C2.address
            GROUP BY address);

このようにWHERE句を加えると、

結果3

    name    | age | address 
------------+-----+---------
 津田桃子   |  56 | 東京都
 南里柴三郎 |  80 | 静岡県
(2 rows)

このように望んだ結果が返ってきました。これが相関サブクエリです💪

参考文献

ミック著 2016 『SQL ゼロから始めるデータベース　第2版』翔泳社