相関サブクエリはいつ(一番)使われるか

f:id:mat5ukawa:20160307004920j:plain

(一番)使われるのは「レコードの存在チェック」

Web 記事を色々眺めて、私なりの答え

(業務システムの SQL を見ていると「このオペレーションは、いつ、何のために使うのか」

を見失うので一回整理したい)

  • 利用者が存在し、各属性を持つ
    • 利用者 ID
    • 年齢(0 以上 100 以下)
    • 名前(255 文字以内の可変長)
CREATE TABLE users (
  userid INTEGER
    NOT NULL,
  age INTEGER
    NOT NULL
    CHECK (age  BETWEEN 0 AND 100),
  name VARCHAR(255)
    NOT NULL,
  PRIMARY KEY(userid)
);
 userid | age |   name    
--------+-----+-----------
      1 |  26 | Akayama
      2 |  30 | Urano
      3 |  25 | Kitano
      4 |  30 | Mashita
      5 |  33 | Okajima
      6 |  26 | Iseno
      7 |  22 | Kashima
      8 |  28 | Takahashi

集計関数を使わない場合

同年齢のユーザー一覧を表示する

SELECT
  *
FROM
  users AS USR
WHERE
  EXISTS (
    SELECT
      1
    FROM
      users AS USRSUB
    WHERE
      USR.userid <> USRSUB.userid
      AND
      USR.age = USRSUB.age
  );
userid | age |  name   
--------+-----+---------
     6 |  26 | Iseno
     1 |  26 | Akayama
     4 |  30 | Mashita
     2 |  30 | Urano

集計関数を使う場合

総ユーザーにおける平均年齢以上の

ユーザー一覧を表示する

SELECT
  AVG(age)
FROM
  users;
avg         
----
27.5
SELECT
  *
FROM
  users AS USR
WHERE
  age >= (
    SELECT
      AVG(USRSUB.age)
    FROM
      users AS USRSUB
  );
userid | age |   name    
--------+-----+-----------
     2 |  30 | Urano
     4 |  30 | Mashita
     5 |  33 | Okajima
     8 |  28 | Takahashi

色々眺めた記事のうち一つ

Best use of Correlated subquery in sqlserver