SQLで結合クエリをもっと自由に！サブクエリ、CASE式、ウィンドウ関数の活用術

2024-07-02

SQLにおけるINNER JOINの反対とは？

一方、INNER JOINの反対となる操作は、すべてのレコードを結合し、条件を満たさないレコードにはNULL値を挿入することです。この操作には、主に以下の3つの方法があります。

LEFT OUTER JOIN

左側のテーブルすべてのレコードを結合の基準とし、右側テーブルとの結合条件に一致するレコードがあれば結合します。
一致するレコードがない場合、右側テーブルの結合列にはNULL値が挿入されます。

SELECT *
FROM 左側テーブル
LEFT OUTER JOIN 右側テーブル
ON 左側テーブル.共通列 = 右側テーブル.共通列;

SELECT *
FROM 右側テーブル
RIGHT OUTER JOIN 左側テーブル
ON 右側テーブル.共通列 = 左側テーブル.共通列;

左側と右側の両方のテーブルすべてのレコードを結合します。

SELECT *
FROM 左側テーブル
FULL OUTER JOIN 右側テーブル
ON 左側テーブル.共通列 = 右側テーブル.共通列;

各結合の種類を使い分ける例

顧客と注文のテーブルを結合する場合、すべての顧客を表示したい場合はLEFT OUTER JOINを使用します。注文履歴がない顧客も表示されます。

INNER JOIN: 結合条件を満たすレコードのみを結合
LEFT OUTER JOIN: 左側のテーブルすべてのレコードを結合し、右側との一致ない場合はNULL値を挿入

これらの結合方法を使い分けることで、必要な情報を漏れなく取得することができます。

テーブル構造

-- 顧客テーブル
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

-- 注文テーブル
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 商品テーブル
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  stock_quantity INT NOT NULL
);

-- 注文明細テーブル
CREATE TABLE order_details (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 顧客データ
INSERT INTO customers (customer_id, name) VALUES
  (1, '田中 太郎'),
  (2, '佐藤 花子'),
  (3, '鈴木 伊藤');

-- 注文データ
INSERT INTO orders (order_id, customer_id, order_date) VALUES
  (1, 1, '2023-10-01'),
  (2, 2, '2023-11-12'),
  (3, 1, '2023-12-25');

-- 商品データ
INSERT INTO products (product_id, name, stock_quantity) VALUES
  (1, 'パソコン', 10),
  (2, 'スマートフォン', 20),
  (3, 'カメラ', 5);

-- 注文明細データ
INSERT INTO order_details (order_id, product_id, quantity) VALUES
  (1, 1, 2),
  (1, 3, 1),
  (2, 2, 1),
  (3, 1, 3);

クエリ

SELECT
  c.name AS 顧客名,
  o.order_date AS 注文日,
  p.name AS 商品名,
  od.quantity AS 注文個数,
  p.stock_quantity AS 在庫数
FROM customers AS c
FULL OUTER JOIN orders AS o
  ON c.customer_id = o.customer_id
FULL OUTER JOIN order_details AS od
  ON o.order_id = od.order_id
FULL OUTER JOIN products AS p
  ON od.product_id = p.product_id;

結果

顧客名 | 注文日 | 商品名 | 注文個数 | 在庫数
------- | -------- | -------- | -------- | --------
田中 太郎 | 2023-10-01 | パソコン | 2 | 10
田中 太郎 | 2023-10-01 | スマートフォン | NULL | 20
田中 太郎 | 2023-10-01 | カメラ | 1 | 5
田中 太郎 | 2023-12-25 | パソコン | 3 | 10
田中 太郎 | 2023-12-25 | スマートフォン | NULL | 20
田中 太郎 | 2023-12-25 | カメラ | NULL | 5
佐藤 花子 | 2023-11-12 | パソコン | NULL | 10
佐藤 花子 | 2023-11-12 | スマートフォン | 1 | 20
佐藤 花子 | 2023-11-12 | カメラ | NULL | 5
鈴木 伊藤 | NULL | パソコン | NULL | 10
鈴木 伊藤 | NULL | スマートフォン | NULL | 20
鈴木 伊藤 | NULL | カメラ | NULL | 5

説明

このクエリは、FULL OUTER JOINを3回使用して、顧客、注文、商品の3つのテーブルを結合しています。
各結合には、ON句で結合条件を指定しています。
SELECT句では、必要な列をすべて選択しています。
顧客名、注文日、**商品

SQLにおけるINNER JOINの反対以外の方法

この機能を実現する方法は、主に以下の3種類が紹介されましたが、状況によってはもっと効率的な方法や、より柔軟なデータ操作を実現する手法が存在します。

サブクエリを使用する

サブクエリを使用する方法では、まず条件を満たすレコードをサブクエリで抽出します。その後、メインクエリで抽出結果と元のテーブルを結合することで、すべてのレコードを結合し、条件を満たさないレコードにはNULL値を挿入することができます。

-- 顧客と注文のテーブルを結合し、すべての顧客を表示する例
SELECT
  c.customer_id,
  c.name,
  o.order_id,
  o.order_date
FROM customers AS c
LEFT OUTER JOIN (
  SELECT order_id
  FROM orders
) AS o
ON c.customer_id = o.customer_id;

CASE式を使用する

CASE式を使用する方法では、各レコードに対して条件を評価し、条件を満たす場合は結合列の値をそのまま出力し、条件を満たさない場合はNULL値を出力します。

-- 顧客と注文のテーブルを結合し、すべての顧客を表示する例
SELECT
  c.customer_id,
  c.name,
  CASE
    WHEN o.order_id IS NULL THEN NULL
    ELSE o.order_id
  END AS order_id,
  CASE
    WHEN o.order_id IS NULL THEN NULL
    ELSE o.order_date
  END AS order_date
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;

ウィンドウ関数を使用する

ウィンドウ関数を使用する方法では、ROW_NUMBERなどの関数を使用して、各レコードに順位を付けます。その後、FIRST_VALUEなどの関数を使用して、各行の最初の値を取得することで、すべてのレコードを結合し、条件を満たさないレコードにはNULL値を挿入することができます。

-- 顧客と注文のテーブルを結合し、すべての顧客を表示する例
SELECT
  c.customer_id,
  c.name,
  FIRST_VALUE(o.order_id) OVER (PARTITION BY c.customer_id ORDER BY o.order_date ROWS BETWEEN PRECEDING 1 FOR CURRENT ROW) AS order_id,
  FIRST_VALUE(o.order_date) OVER (PARTITION BY c.customer_id ORDER BY o.order_date ROWS BETWEEN PRECEDING 1 FOR CURRENT ROW) AS order_date
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;