質問によっては、回答するまでに 2 つのステップが必要になる場合があります。 「平均を上回る注文はどれですか?」まず平均を見つけて、それから各注文をそれと比較する必要があります。 SQL サブクエリ を使用すると、両方のステップを 1 つのステートメントとして記述できます。

このチュートリアルでは、2 つの小さな表を使用して各タイプを説明します。これらのクエリを SQLite 3.50 で実行し、表示された結果はその実行から得られたものです。標準のサブクエリ構文は広くサポートされており、データベースが異なる箇所を指摘します。

サブクエリとは何ですか?

サブクエリは、内部クエリまたはネスト クエリとも呼ばれ、別のステートメント内に丸括弧で囲まれた SELECT ステートメントです。概念的には、内部クエリは外部クエリで使用される結果を生成します。

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

サンプルテーブル

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Dev には命令がないことに注意してください。この詳細は、以下のいくつかの例で重要になります。

サブクエリの種類の概要

スカラーサブクエリ

スカラー サブクエリは値を 1 つだけ返すため、単一の値が意味をなす場合にはどこでも使用できます。これは、以前のクエリとその結果です。すべての注文の平均は 980 です。

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

WHERE では直接集計を使用できないため、WHERE total > AVG(total) を記述することはできません。サブクエリは値を個別に計算することでこれを回避します。

「スカラー」サブクエリが複数の行を返した場合はどうなるでしょうか? PostgreSQL、MySQL、SQL Server などのほとんどのデータベースではエラーが発生します。 SQLite はそうではありません。私たちのテストでは、最初の行のみがサイレントに使用されましたが、実際のバグが隠れている可能性があるため、この動作に依存しないでください。

入っている、入っていない

内部クエリがリストを返す場合は、IN を使用します。これにより、少なくとも 1 つの注文が 1000 を超える顧客が検索されます。

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN はその逆で、注文したことがない顧客を見つけます。

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

ここではこれで機能しますが、NOT IN には危険なエッジケースがあり、これについては以下で説明します。

FROM のサブクエリ

FROM のサブクエリは一時テーブルとして機能し、多くの場合、派生テーブルと呼ばれます。これは、各都市の顧客あたりの平均支出額を求める次のクエリのように、2 回集計する必要がある場合に便利です。

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

内側のクエリは各顧客を合計し、外側のクエリはそれらの合計を平均します。一部のデータベースではエイリアスが必要なため、派生テーブルにエイリアスを付けることは良い習慣です。 GROUP BY が初めての場合は、SQL GROUP BY と HAVING を参照してください。

SELECT のサブクエリ

スカラー サブクエリでは計算列を追加することもできます。これは顧客ごとの注文をカウントします。

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev はゼロで表示されます。 Dev には一致する行がないため、内部結合では Dev が完全に削除されます。 LEFT JOIN と GROUP BY を使用しても同じ結果が得られます。これについては、SQL 結合の説明 で説明します。

相関サブクエリ

内部クエリが外部クエリの c.id を参照しているため、前の例も相関しています。非相関サブクエリは単独で実行できます。相関関係のあるものは、現在の外側の行の値に依存するため、できません。

これにより、同じ顧客自身の平均を上回る注文が見つかります。

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

読み方は次のとおりです。注文 101 の場合、内部クエリは顧客 1 の注文 (1200 と 800) を平均し、1000 となります。1200 の方が大きいため、行は保持されます。次数 102 については、同じ平均が適用されますが、800 はそれより大きくないため、削除されます。顧客 2 の 1 つの注文はそれ自体の平均に等しいため、これも削除されます。

「行ごとに 1 回」はロジックを記述しており、クエリ オプティマイザーはこれらをはるかに効率的なものに書き換えることがよくあります。それでも、遅い相関サブクエリは調査する価値があります。内側の列のインデックス (ここでは orders.customer_id) は、大きな違いを生む可能性があります。 SQL インデックスの説明を参照してください。

存在するものと存在しないもの

EXISTS は、はいまたはいいえで答える質問をします。内部クエリは少なくとも 1 行を返しますか?最初の一致で停止する可能性があり、内部で何を選択するかは重要ではないため、SELECT 1 と書かれることがよくあります。

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

NOT IN NULL トラップ

これは、サブクエリの最も一般的なバグの 1 つです。 1 つの実際の ID と 1 つの NULL を持つテーブルを作成し、それに対して両方のフォームを実行しました。

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

なぜ最初のものは何も返さないのでしょうか? id NOT IN (1, NULL)はid <> 1 AND id <> NULLを意味します。 NULL との比較は真ではなく未知であるため、条件全体が真になることはあり得ません。エラー メッセージや警告はなく、ありそうな空の結果が表示されるだけです。

サブクエリ vs JOIN vs CTE

多くのクエリは複数の方法で記述できます。先ほど、IN を使用して大量の注文がある顧客を見つけました。結合を使用した場合の同じ結果は次のとおりです。

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

DISTINCTに注目してください。顧客に複数の一致がある場合、結合では重複が生成される可能性がありますが、IN と EXISTS では重複が生成されません。

パフォーマンスはデータベースやデータによって異なり、最新のオプティマイザーは多くの場合、同様の計画を作成します。したがって、最初に最も明確なフォームを選択し、それが遅いことが判明した場合は EXPLAIN をチェックしてください。

よくある間違い

• 複数行の結果で = を使用します。 IN を使用するか、内部クエリが 1 行を返すようにしてください。
• Null 許容列で NOT IN を使用すると、 警告なしにすべての行が失われる可能性があります。
• 不明瞭なエイリアスの使用。 相関クエリでは、すべての列を修飾しないと、間違ったテーブルにバインドされる可能性があります。
• 深いネスト。 3 つのレベルは読みにくいため、CTE に変換します。
• 内部に ORDER BY を追加します。 通常、外部の結果には影響せず、一部のデータベースでは拒否されます。

まとめ

サブクエリは、2 段階の質問を表現する自然な方法です。スカラー サブクエリと IN から始めて、一度に 1 つの外側の行を推論して、相関サブクエリを練習します。 NULL が可能な場合は常に NOT EXISTS を思い出してください。練習するには、サブクエリ、結合、CTE を使用して 3 つの方法で 1 つの質問を作成し、結果と読みやすさの両方を比較します。

よくある質問

学び続ける