サブクエリと結合を使いこなして、SQLスキルアップを目指そう

サブクエリと結合：パフォーマンスと使い分け

SQLで複数のテーブルからデータを抽出する際、サブクエリと結合はどちらも重要なテクニックです。しかし、それぞれ異なる動作とパフォーマンス特性を持つため、状況に応じた使い分けが重要です。

サブクエリとは？

サブクエリは、SELECT文の中に埋め込まれた別のSELECT文です。主クエリから独立したクエリとして実行され、その結果が主クエリの条件や演算に使用されます。

結合とは？

結合は、複数のテーブルを論理的に結合し、1つのテーブルのように扱う操作です。共通する列に基づいてテーブルを結合することで、複数のテーブルにまたがるデータを効率的に抽出できます。

パフォーマンス

一般的に、結合の方がサブクエリよりもパフォーマンスが優れています。これは、結合はテーブルを直接結合するため、データアクセスが1回で済むからです。一方、サブクエリは主クエリとサブクエリがそれぞれ別々に実行されるため、データアクセスが2回以上必要になります。

使い分け

サブクエリと結合を使い分けるポイントは、以下の3つです。

• データ構造: サブクエリは、複雑なデータ構造を抽出するのに適しています。例えば、ある列の値が別のテーブルの複数の列の値と一致するレコードを抽出したい場合、サブクエリを使うと簡潔に記述できます。
• パフォーマンス: パフォーマンスが重要な場合は、結合を使う方が有利です。
• 可読性: 可読性を重視する場合は、サブクエリよりも結合の方が分かりやすいコードになる場合があります。

例

サブクエリ

SELECT *
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE country = '日本'
);

この例では、日本在住の顧客の注文情報のみを抽出しています。

結合

SELECT *
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id
WHERE customers.country = '日本';

この例では、サブクエリと同じ結果を結合を使って表現しています。

• orders テーブル: 注文情報
  • order_id: 注文ID (主キー)
  • customer_id: 顧客ID
  • product_id: 商品ID
  • order_date: 注文日
• customers テーブル: 顧客情報
  • customer_name: 顧客名
  • country: 国

サブクエリ

-- 日本在住の顧客の注文情報のみを抽出

SELECT *
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE country = '日本'
);

結合

-- 日本在住の顧客の注文情報のみを抽出

SELECT *
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id
WHERE customers.country = '日本';

サブクエリと結合の比較

-- サブクエリ

SELECT *
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE country = '日本'
);

-- 結合

SELECT *
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id
WHERE customers.country = '日本';

サブクエリと結合の使い分け

• 複雑なデータ構造: サブクエリは、複雑なデータ構造を抽出するのに適しています。

• 特定の商品を注文した顧客の情報を抽出
• 過去1年間の注文情報の中で、最も高額な注文情報を抽出
• 顧客の注文数と平均注文金額を抽出

サブクエリと結合の代替方法

代替方法

• CASE式: 比較的単純な条件でデータを抽出したい場合は、CASE式を使うとコードが簡潔になります。
• EXISTS: サブクエリで存在確認を行う場合は、EXISTSキーワードを使うと簡潔に記述できます。
• ウィンドウ関数: 特定の列の集計値などを考慮したデータ抽出を行う場合は、ウィンドウ関数を使うと効率的に処理できます。
• CTE (Common Table Expressions): 複雑なサブクエリを繰り返し使用する場合は、CTEを使うとコードを分かりやすく整理できます。

CASE式

-- 特定の商品を注文した顧客の情報を抽出

SELECT *
FROM customers
WHERE country = '日本'
AND (product_id = 1 OR product_id = 2);

EXISTS

-- 過去1年間の注文情報の中で、最も高額な注文情報を抽出

SELECT *
FROM orders
WHERE order_date >= '2023-04-05'
AND EXISTS (
  SELECT *
  FROM orders
  WHERE order_date >= '2023-04-05'
  AND order_amount > orders.order_amount
);

ウィンドウ関数

-- 顧客の注文数と平均注文金額を抽出

SELECT customer_id,
       COUNT(*) AS order_count,
       AVG(order_amount) AS average_order_amount
FROM orders
GROUP BY customer_id;

CTE

-- 複雑なサブクエリを繰り返し使用する

WITH recursive t AS (
  SELECT *
  FROM orders
  WHERE order_date >= '2023-04-05'
  UNION ALL
  SELECT *
  FROM t
  INNER JOIN orders
  ON t.order_id = orders.parent_order_id
)
SELECT *
FROM t;

上記の例はあくまで基本的な例です。実際には、より複雑な条件や演算を組み合わせることで、様々なデータ抽出を行うことができます。