Zenn
💡

学習備忘録〜O'Reilly「初めてのSQL」9章 〜

2024/01/02に公開
SQL
tech

はじめに

この備忘録は、新卒1年目の初学者バックエンドエンジニアの学習記録のためにつけているものです。
解釈違いや、誤情報がある可能性があります。見つけた際にはご指摘をお願いします。

概要

サブクエリはSQLの4種類のデータ分の全てで利用できる強力なツールである。サブクエリを利用すれば、データのフィルタリング、値の生成、そして一時的なデータセットの生成が可能。

サブクエリとは何か

サブクエリとは、別のSQL文に含まれているクエリのこと。サブクエリは常に丸括弧で囲まれ、通常は外側の文よりも先に実行される。
通常のクエリと同様に、サブクエリは以下のいずれかで構成された結果セットを返す。

  • 列が1つだけ含まれた１行のデータ
  • 列が1つだけ含まれた複数行のデータ
  • 複数の列が含まれた複数行のデータ
mysql> SELECT customer_id, first_name, last_name
    -> FROM customer
    -> WHERE customer_id = (SELECT MAX (customer_id) FROM customer);

下記部分

(SELECT MAX (customer_id) FROM customer)

非相関サブクエリ

非相関サブクエリは単体でも実行できるもので、外側の文の何かを参照しない。update文やdelete文を作成する場合を除いて、出くわすほとんどのサブクエリは非相関サブクエリになる。

mysql> SELECT city_id, city
    -> FROM city
    -> WHERE country_id <>
    ->   ( SELECT country_id FROM country WHERE country = 'India');

複数行/単一列のサブクエリ

サブクエリが複数の行を返す場合、そのサブクエリを等号条件のどちらかのオンペランドとして使うことはできない。ただし、条件を組み立てるときに利用できる演算子が4つある。

in演算子とnot演算子

in演算子は、ある式が式の集合の中から見つかるかどうかをチェックできる

下記のような形で使う

mysql> SELECT country_id
    -> FROM country
    -> WHERE country IN ('Canada', 'Mexico' );

all演算子

all演算子は、ある値を集合内のすべての値と比較できる

mysql> SELECT first_name, last_name
    -> FROM customer
    -> WHERE customer_id <> ALL
    ->    ( SELECT customer_id
             FROM payment
	     WHERE amount = 0 );

これと同じ結果が得られるのが、not in演算子

mysql> SELECT first_name, last_name
    -> FROM customer
    -> WHERE customer_id NOT IN
    ->    ( SELECT customer_id
             FROM payment
	     WHERE amount = 0 );

any演算子

all演算子と同様に、any演算子でも、ある値を値の集合と比較できる。ただし、all演算子を使った条件がtrueと評価されるのは集合内のすべてのメンバーに対して比較が成立した場合だけだが、any演算子を使った条件は比較が1つでも成立した時点でtrueと評価される。

mysql> SELECT customer_id, sum(amount)
    -> FROM payment
    -> GROUP BY customer_id
    -> HAVING sum(amount) > ANY
    ->    (SELECT sum(p.amount)
    ->     FROM payment p
    ->       INNER JOIN customer c
    ->       ON p.customer_id = c.customer_id
    ->       INNER JOIN address a
    ->       ON c.address_id = a.address_id
    ->       INNER JOIN city ct
    ->       ON a.city_id = ct.city_id
    ->       INNER JOIN country co
    ->       ON ct.country_id = co.country_id
    ->    WHERE co.country IN ('Bolivia', 'Paraguay', 'Chile')
    ->    GROUP BY co.country
    ->    );

複数列のサブクエリ

一旦すぐには使わないため後回し

相関サブクエリ

上記までの非相関サブクエリはどれも外側の分には依存していなかった。つまり、それらのサブクエリを単体で実行し、結果を調べることができた。
一方で、相関サブクエリは、外側の分の列を1つ以上参照するという点で、外側の分に依存している。

mysql> SELECT c.first_name, c.last_name
    -> FROM customer c
    -> WHERE 20 =
    ->   ( SELECT count(*)
    ->     FROM rental r
    ->     WHERE r.customer_id = c.customer_id );

この部分の最後の方で参照しているc.customer_idが相関サブクエリである。
このサブクエリを実行するためには、外側のクエリがc.customer_idの値を提供しなければならないから。

exist演算子

分量に関係なくある関数が存在するかどうかを突き止めたいときに使う。
具体例は割愛。後日記載

サブクエリを使う状況

データソースとしてのサブクエリ

サブクエリとテーブルの併用は、クエリを記述すときに強力な武器の1つになる
一旦割愛。後日記載

まとめ

  • 1行/1列、複数行/複数列の結果セットを返すサブクエリ
  • 外側の分に依存しないサブクエリ（非相関サブクエリ）
  • 外側の分の列を1つ以上参照するサブクエリ（相関サブクエリ）
  • 比較演算子や特別な演算子（in,noy in,exists, not exists）を活用した条件で使われるサブクエリ
  • select文、update文、delete文、insert文で使われるサブクエリ
  • クエリないの他のテーブル（またはサブクエリ）に結合できる結果セットを返すサブクエリ
  • テーブルに挿入する値、またはクエリの結果赤の列に挿入する値を生成できるサブクエリ
  • クエリのselect句、from句、where句、having句、order by句で使われるサブクエリ

等々、サブクエ地は非常に用途の広いツールである。
複雑なSQL文を記述するたびに「サブクエリをどのように活用できるか」と考えていくことが大切。

Discussion