PostgreSQL - 子クエリ

こんにちは、データベースの愛好家さんたち！今日は、PostgreSQLの面白い世界である子クエリについて掘り下げます。子クエリは、メインクエリがデータを取得または操作するのを助ける小さな助け手として考えられます。好きなスーパーヒーロームovieの信頼のサイドkickのようなものです。常に注目の的ではないにしても、仕事を完了するために不可欠な存在です！

SELECT 文での子クエリ

まず、最も一般的な子クエリの使用方法を見てみましょう。SELECT文の中で使います。想像してみてください、書店を経営していて、平均価格を超える本を知りたい場合。以下のようにやります：

SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

このクエリでは、(SELECT AVG(price) FROM books)の部分が私たちの子クエリです。これはすべての本の平均価格を計算し、メインクエリはこの値を使って結果をフィルタリングします。

以下に分解します：

1. 子クエリは平均価格を計算します。
2. メインクエリは各本の価格をこの平均と比較します。
3. 平均を超える価格を持つ本だけが返されます。

もう一つの例を見てみましょう。すべての注文をした顧客を見つけたい場合：

SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

この場合、私たちの子クエリ(SELECT DISTINCT customer_id FROM orders)は、注文をした顧客IDのリストを返します。メインクエリはこのリストを使って顧客テーブルをフィルタリングします。

INSERT 文での子クエリ

次に、INSERT文での子クエリの使用を見てみましょう。例えば、売上高が1000冊以上の本を含む「Featured Books」テーブルを作成し、それを埋めたい場合：

INSERT INTO featured_books (book_id, title, author)
SELECT book_id, title, author
FROM books
WHERE book_id IN (SELECT book_id FROM sales WHERE copies_sold > 1000);

ここで、私たちの子クエリ(SELECT book_id FROM sales WHERE copies_sold > 1000)は、売上高が1000冊以上の本を見つけます。メインクエリはこれらの本の詳細を「Featured Books」テーブルに挿入します。

UPDATE 文での子クエリ

子クエリはUPDATE文でも非常に役立ちます。例えば、5冊以上の本を書いた著者の本すべてに10%の割引を与えたい場合：

UPDATE books
SET price = price * 0.9
WHERE author_id IN (
SELECT author_id
FROM books
GROUP BY author_id
HAVING COUNT(*) > 5
);

この例では、子クエリが5冊以上の本を書いた著者を見つけます。メインクエリはこれらの著者の本の価格を更新します。

DELETE 文での子クエリ

最後に、DELETE文での子クエリの使用を見てみましょう。例えば、昨年以降購入していない顧客のすべての注文を削除したい場合：

DELETE FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE last_purchase_date < CURRENT_DATE - INTERVAL '1 year'
);

ここで、私たちの子クエリが昨年以降購入していない顧客を見つけます。メインクエリはこれらの顧客のすべての注文を削除します。

一般的な子クエリメソッド

以下は、PostgreSQLで一般的に使用される子クエリメソッドの表です：

メソッド	説明	例
IN	値が子クエリの結果セットに含まれているかどうかをチェック	WHERE id IN (SELECT id FROM table)
EXISTS	子クエリが行を返すかどうかをチェック	WHERE EXISTS (SELECT 1 FROM table WHERE condition)
ANY	子クエリの値のいずれかが条件を満たす場合にtrueを返す	WHERE column > ANY (SELECT column FROM table)
ALL	子クエリのすべての値が条件を満たす場合にtrueを返す	WHERE column > ALL (SELECT column FROM table)

練習は完璧を生みます！自分自身でクエリを書いて、さまざまなシナリオを試してみてください。間違えても構いません。それが私たちが学び、成長する方法です。

最後に、私の教え方の経験から小さな話を披露します。ある生徒が子クエリに苦戦していて、メインクエリと子クエリを混同していました。そこで、私は彼にロシアのメイドインドールを思い浮かべるように言いました。最も小さなドール（子クエリ）が最も大きなドール（メインクエリ）に収まるように考えましょう。この視覚的な助けが彼に役立ち、すぐに複雑なクエリを書けるようになりました！

このチュートリアルがPostgreSQLの子クエリを理解するのに役立つことを願っています。クエリを続けて、学び続けて、最も重要なのは、データベースを楽しむことです！

Credits: Image by storyset