相関サブクエリを用いるのに日本語→WHERE句の順で考えた

初投稿です。初心者です。

本記事の3点要約

• 「相関サブクエリ」というものを使うと、SQLを使ってデータテーブル内のある行の値を他の行の値と比べることができる
• 相関サブクエリを使うときは、同じデータテーブル内で2回FROM句を使うときは、わかりやすいように AS 句を使って名称を分けることが望ましい

引っかかったこと

SQLの学習サイトで下記の問題にあたり(本頁末尾にソース記載)、つっかえた。

国名(name)、州名(continent)、人口(population)の一覧のデータテーブル'world'があった時に、「同じ州の中でどの国と比べても3倍の人口を持つ国」を探し、その国名と州名を答えよ」

というもの。

SELECT
  name,
  continent
FROM
  world
WHERE
  hogehoge

と書くのだろうと思ったが、そのhogehogeに何を書くのかでだいぶ悩んだ。

解決策：相関サブクエリ

先に回答を書くと以下のようである。

SELECT
  name,
  continent
FROM
  world as A
WHERE 
  A.population / 3 > ALL(
    SELECT
      population
    FROM
      world as B
    WHERE
      B.continent = A.continent
      AND B.name != A.name
  )

これに行き着くために、SQLの初心者である自分は「何をWHERE句で表現したいのか？」を日本語で表し、それをクエリに変換する作業を行った。

脳内で考えたこと

まず、WHERE句で書きたいと思ったのが、
「(1)州内の人口を比較したいから持ってきてほしい。(2)worldテーブルからね。(3)最終的に求めたい国に対して、大陸が同じ国の人口だけを比べる。(4)あと、自分自身と比べちゃだめ。
(5)これらを州ごとに調べて、人口に3倍差がついているやつだけ(を出して)」
という内容。

相関サブクエリとは何か

• 1つのクエリ(SELECTからお尻まで)の中で別のクエリ(SELECTからお尻まで)を呼び出すからサブクエリ。
• 上記でいう、WHERE句のALL()内の文のこと。
  前述の内容がサブクエリで表すべき内容なんだなと感じ、以下の通り日本語→WHERE句へ変換した。

WHERE
 A.population / 3 > ALL( -- ※(最後に読む)
    SELECT 
      population  -- (1)州内の人口を比較したいから持ってきてほしい
    FROM
      world as B  -- (2)worldテーブルからね。サブクエリとして呼び出すからわかりやすくBと命名するよ
    WHERE
      B.continent = A.continent -- (3)最終的に求めたい国に対して、大陸が同じ国の人口だけを比べるんだよね？
      AND B.name != A.name -- (4)自分自身と比べちゃだめだよね
    -- ※：(5)これらに該当する行をすべて比較して、3倍差がついているやつだけを表示するよ
  )

大切だと思われるところ

• 相関サブクエリのようなSQL独特の言い回しを、初学者がいきなり思いつくのには無理がある。まずは日本語にて、抽出したいデータテーブルのイメージをはっきり言語化させてから、それを忠実に落とし込んだクエリを書くようにする。
• 「相関サブクエリを呼び出すときはAS句を使ってデータテーブルの名称を分けようね」と暗記するのではなく、「難しいクエリを書くなら、データテーブルを呼び出すたびに名前をつけておいたほうが後々便利そうだと感じよう」というマインドを持つことが大切なのではないか。

所感

• SQLZoo難しい...難しくない？
• SQLZoo最高...最高じゃない？
• SQLZoo...最高やな！

　ソース

• SQLZoo(最高のSQL学習サイト[英語です])
• SQLZooの本章の回答