SQL COALESCE 関数: NULL 値を効果的に処理する

概要: このチュートリアルでは、SQL COALESCE 関数を紹介し、実際のシナリオでこの関数を適用する方法を説明します。

SQL COALESCE 関数の紹介

COALESCE 関数は、複数の引数を受け取り、最初の非 NULL 引数を返します。COALESCE 関数の構文を以下に示します。

COALESCE(argument1, argument2,...);Code language: SQL (Structured Query Language) (sql)

COALESCE 関数は、引数を左から右に評価します。最初の非 NULL 引数が見つかるまで評価を停止します。つまり、残りの引数はまったく評価されません。

すべての引数が NULL の場合、COALESCE 関数は NULL を返します。

次のステートメントは、1 が最初の非 NULL 引数であるため、1 を返します。

SELECT COALESCE(1,2,3); -- return 1Code language: SQL (Structured Query Language) (sql)

次のステートメントは、NULL と評価されない最初の文字列引数であるため、Not NULL を返します。

SELECT COALESCE(NULL,'Not NULL','OK'); -- return Not NULLCode language: SQL (Structured Query Language) (sql)

次のステートメントを使用する場合

SELECT 1/0; -- division by zeroCode language: SQL (Structured Query Language) (sql)

ゼロ除算エラーが発生します。

ただし、次のステートメントは 1 を返し、エラーは発生しません

SELECT COALESCE(1,1/0); -- return 1Code language: SQL (Structured Query Language) (sql)

これは、COALESCE 関数が短絡評価されるためです。最初の非 NULL 引数が見つかった後、残りの引数の評価を停止します。

MySQL、PostgreSQL、Oracle、Microsoft SQL Server、Sybase など、ほとんどすべての関係データベースシステムが COALESCE 関数をサポートしています。

COALESCE 関数は、NVL 関数の最も汎用的な関数であり、NVL 関数の代わりに使用できることに注意してください。

SQL COALESCE の例

次の構造とデータを持つ products テーブルがあるとします

CREATE TABLE products (
	ID INT PRIMARY KEY,
	product_name VARCHAR(255) NOT NULL,
	product_summary VARCHAR(255),
	product_description VARCHAR(4000) NOT NULL,
	price NUMERIC (11, 2) NOT NULL,
	discount NUMERIC (11, 2),
	CHECK (net_price >= discount)
);Code language: SQL (Structured Query Language) (sql)

INSERT INTO products (
	ID,
	product_name,
	product_summary,
	product_description,
	price,
	discount
)
VALUES
	(
		1,
		'McLaren 675LT',
		'Inspired by the McLaren F1 GTR Longtail',
		'Performance is like strikin and the seven-speed dual-clutch gearbox is twice as fast now.',
		349500,
		1000
	),
	(
		2,
		'Rolls-Royce Wraith Coupe',
		NULL,
		'Inspired by the words of Sir Henry Royce, this Rolls-Royce Wraith Coupe is an imperceptible force',
		304000,
		NULL
	),
	(
		3,
		'2016 Lamborghini Aventador Convertible',
		NULL,
		'Based on V12, this superveloce has been developed as the Lamborghini with the sportiest DNA',
		271000,
		500
	);Code language: SQL (Structured Query Language) (sql)

SELECT * FROM products;Code language: SQL (Structured Query Language) (sql)

NULL 値の置換に SQL COALESCE を使用する

データベーステーブルのデータを操作する場合、NULL 値のデフォルト値を置き換えるために COALESCE 関数をよく使用します。

products テーブルのすべての情報を含む Web ページに製品を表示する必要があるとします。一部の製品には概要がない場合がありますが、他の製品には概要があります。

この場合、COALESCE 関数を使用して製品概要を返し、製品概要が提供されていない場合は、製品説明の最初の 50 文字を取得できます。

SELECT
	ID,
	product_name,
	COALESCE (
		product_summary,
		LEFT (product_description, 50)
	) excerpt,
	price,
	discount
FROM
	products;Code language: SQL (Structured Query Language) (sql)

CONCAT 関数を使用して抜粋の末尾に (...) を追加することで、ユーザーが読んでいるテキストが抜粋に過ぎず、「続きを読む」リンクをクリックすればさらにコンテンツがあることをより明確に示すことができます。

SELECT
	ID,
	product_name,
	COALESCE (
		product_summary,
		CONCAT(
			LEFT (product_description, 50),
			'...'
		)
	) excerpt,
	price,
	discount
FROM
	products;Code language: SQL (Structured Query Language) (sql)

式での SQL COALESCE 関数の使用

すべての製品の正味価格を計算する必要があるとします。次の クエリ を考え出しました

SELECT 
    id, 
    product_name, 
    (price - discount) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

Rolls-Royce Wraith Coupe の正味価格は NULL です。これは、この製品の割引が NULL であり、計算でこの NULL 値を使用すると、NULL 値になるためです。

これを修正するには、discount 列のすべての NULL 値を 0 に更新します。

UPDATE products 
SET 
    discount = 0
WHERE
    discount IS NULL;Code language: SQL (Structured Query Language) (sql)

または、次のように COALESCE 関数を使用できます

SELECT 
    id, 
    product_name, 
    price,
    discount,
    (price - COALESCE(discount,0)) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

正味価格が正しく計算されるようになりました。

SQL COALESCE と CASE 式

COALESCE 関数は、CASE 式の構文糖衣です。つまり、式

COALESCE(argument1,argument2,argument3);Code language: SQL (Structured Query Language) (sql)

は、次の CASE 式を使用して書き直すことができます

CASE
  WHEN (argument1 IS NOT NULL) THEN argument1
  WHEN (argument2 IS NOT NULL) THEN argument2
  ELSE argument3
ENDCode language: SQL (Structured Query Language) (sql)

たとえば、CASE 式を使用して、価格と割引から正味価格を計算するクエリを次のように書き直すことができます

SELECT 
    id,
    product_name,
    price,
    discount,
    (price - 
      CASE
        WHEN discount IS NOT NULL THEN discount
        ELSE 0
    END) AS net_price
FROM
    products;Code language: SQL (Structured Query Language) (sql)

クエリは、COALESCE 関数を使用したものと同じ結果を返します。

このチュートリアルでは、SQL COALESCE 関数を使用してデータベーステーブルの NULL 値を処理する方法を学習しました。