【MySQL/SQL/SQL Server】LEFT OUTER JOINでNULLをデフォルト値に置き換える方法を徹底解説！

2024-06-24

MySQL、SQL、SQL ServerにおけるLEFT OUTER JOINで返されるデフォルトのNULL値の置換

この場合、結果セットのNULL値をデフォルト値やその他の値に置き換えることが必要になる場合があります。以下、MySQL、SQL Server、共通の代替方法について説明します。

MySQLでは、COALESCE() 関数を使用して、LEFT OUTER JOINで返されるNULL値をデフォルト値に置き換えることができます。

SELECT
  t1.col1,
  t2.col2,
  COALESCE(t2.col3, 'デフォルト値') AS col3
FROM
  t1
LEFT OUTER JOIN
  t2
ON
  t1.id = t2.id;

このクエリは、t1.col1、t2.col2、t2.col3 の値を結果セットに返し、t2.col3 がNULLの場合、'デフォルト値' に置き換えます。

SELECT
  t1.col1,
  t2.col2,
  ISNULL(t2.col3, 'デフォルト値') AS col3
FROM
  t1
LEFT OUTER JOIN
  t2
ON
  t1.id = t2.id;

共通代替方法

SELECT
  t1.col1,
  t2.col2,
  CASE WHEN t2.col3 IS NULL THEN 'デフォルト値' ELSE t2.col3 END AS col3
FROM
  t1
LEFT OUTER JOIN
  t2
ON
  t1.id = t2.id;

補足:

上記の例では、デフォルト値として文字列を使用していますが、数値やその他のデータ型に置き換えることもできます。
必要に応じて、複数の列に対してNULL値の置換を適用できます。

これらの方法を理解することで、LEFT OUTER JOINで取得したデータからNULL値を効果的に処理し、分析に役立つ情報に変換することができます。

サンプルコード：LEFT OUTER JOIN で返されるデフォルトの NULL 値を置き換える

テーブル定義

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

データ

INSERT INTO customers (customer_id, name, email)
VALUES
  (1, 'John Doe', '[email protected]'),
  (2, 'Jane Doe', '[email protected]'),
  (3, 'Peter Jones', NULL);

INSERT INTO orders (order_id, customer_id, order_date, amount)
VALUES
  (1, 1, '2023-10-05', 100.00),
  (2, 1, '2023-11-15', 50.00),
  (3, 2, '2023-12-24', 75.00);

クエリ

以下のクエリは、customers テーブルのすべての行を返し、一致する orders テーブルの行があれば結合します。一致する行がない場合、order_date と amount 列は NULL 値で埋め込まれます。

SELECT
  c.customer_id,
  c.name,
  c.email,
  o.order_date,
  COALESCE(o.amount, 0.00) AS amount
FROM
  customers AS c
LEFT OUTER JOIN
  orders AS o
ON
  c.customer_id = o.customer_id;

結果

customer_id | name       | email           | order_date | amount
-----------+------------+----------------+------------+---------
1           | John Doe   | [email protected] | 2023-10-05 | 100.00
1           | John Doe   | [email protected] | 2023-11-15 | 50.00
2           | Jane Doe   | [email protected] | 2023-12-24 | 75.00
3           | Peter Jones | NULL            | NULL       | 0.00

このクエリでは、COALESCE() 関数を使用して、order_date と amount 列における NULL 値を 0 に置き換えています。

代替方法

上記の方法に加えて、以下のような代替方法もあります。

ISNULL() 関数を使用する (SQL Server で推奨)
CASE 式を使用する

-- ISNULL() を使用する場合
SELECT
  c.customer_id,
  c.name,
  c.email,
  ISNULL(o.order_date, 'N/A') AS order_date,
  ISNULL(o.amount, 0.00) AS amount
FROM
  customers AS c
LEFT OUTER JOIN
  orders AS o
ON
  c.customer_id = o.customer_id;

-- CASE 式を使用する場合
SELECT
  c.customer_id,
  c.name,
  c.email,
  CASE WHEN o.order_date IS NULL THEN 'N/A' ELSE o.order_date END AS order_date,
  CASE WHEN o.amount IS NULL THEN 0.00 ELSE o.amount END AS amount
FROM
  customers AS c
LEFT OUTER JOIN
  orders AS o
ON
  c.customer_id = o.customer_id;

これらの代替方法はいずれも、LEFT OUTER JOIN で返される NULL 値を置き換えるために有効です。

LEFT OUTER JOIN で返されるデフォルトの NULL 値を置き換えるその他の方法

サブクエリを使用して、LEFT OUTER JOIN で返される各行に対してデフォルト値を計算することができます。

SELECT
  c.customer_id,
  c.name,
  c.email,
  (
    SELECT
      CASE WHEN o2.order_date IS NULL THEN 'N/A' ELSE o2.order_date END
    FROM
      orders AS o2
    WHERE
      o2.customer_id = c.customer_id
    ORDER BY
      o2.order_date DESC
    LIMIT
      1
  ) AS order_date,
  (
    SELECT
      CASE WHEN o3.amount IS NULL THEN 0.00 ELSE o3.amount END
    FROM
      orders AS o3
    WHERE
      o3.customer_id = c.customer_id
    ORDER BY
      o3.order_date DESC
    LIMIT
      1
  ) AS amount
FROM
  customers AS c;

このクエリは、customers テーブルの各行に対して、orders テーブルから最新の order_date と amount を取得し、NULL 値を置き換えます。

WITH 句を使用して、中間結果セットを作成し、その結果セットを使用して LEFT OUTER JOIN で返される NULL 値を置き換えることができます。

WITH latest_orders AS (
  SELECT
    customer_id,
    MAX(order_date) AS order_date,
    MAX(amount) AS amount
  FROM
    orders
  GROUP BY
    customer_id
)

SELECT
  c.customer_id,
  c.name,
  c.email,
  lo.order_date,
  lo.amount
FROM
  customers AS c
LEFT OUTER JOIN
  latest_orders AS lo
ON
  c.customer_id = lo.customer_id;

このクエリは、latest_orders という中間結果セットを作成し、その結果セットを使用して customers テーブルと結合し、NULL 値を置き換えます。

ビューを使用して、LEFT OUTER JOIN で返される NULL 値を置き換えるためのカスタムロジックをカプセル化することができます。

CREATE VIEW customers_with_latest_orders AS
SELECT
  c.customer_id,
  c.name,
  c.email,
  (
    SELECT
      CASE WHEN o2.order_date IS NULL THEN 'N/A' ELSE o2.order_date END
    FROM
      orders AS o2
    WHERE
      o2.customer_id = c.customer_id
    ORDER BY
      o2.order_date DESC
    LIMIT
      1
  ) AS order_date,
  (
    SELECT
      CASE WHEN o3.amount IS NULL THEN 0.00 ELSE o3.amount END
    FROM
      orders AS o3
    WHERE
      o3.customer_id = c.customer_id
    ORDER BY
      o3.order_date DESC
    LIMIT
      1
  ) AS amount
FROM
  customers AS c;

SELECT * FROM customers_with_latest_orders;