SQL の COALESCE () 関数について

テクノロジーの成長と進化に伴い、開発者として最新のトレンドを常に最新の状態に保つことが重要です。初心者でも専門家でも、文字列操作をしっかり理解していれば、データを準備し (たとえば、既存のフォームとは異なるフォームを生成してビジネスで使用できるようにする)、組み込みの SQL サーバー関数を使用してデータを管理するのに役立ちます。

データ操作に加えて、データ セットを調べ、データ値を評価し、それらをエンコードまたはデコードして、より意味のあるデータを取得することができます。その結果、これは、データ セット内の欠損値をナビゲートし、計算への影響を理解し、データ プロセスの全体的な作業を合理化して、操作結果を台無しにする可能性のある Null 値を回避するのに役立ちます。

このガイドでは、複雑なプログラムの構築に役立つ SQL の結合関数について説明します。この投稿では、読者が SQL に遭遇して操作したことがあり、この特定の関数についての理解を深めたいと考えていることを前提としています。当社の一連の SQL ガイドは、すぐに使い始めるのに役立ちます。

SQL の COALESCE () とその用途とは何ですか?

SQL の結合関数は、リストと同様に指定された順序でパラメータ (引数) を評価し、最初の非 null 値を返します。簡単に言えば、この関数はリストを順番に評価し、最初の非 null 値のインスタンスで終了します。リスト内のすべての引数が null の場合、関数は NULL を返します。

さらに、この関数は包括的であり、MYSQL、Azure SQL Database、Oracle、PostgreSQL などの他のデータベースでもサポートされています。

次のような場合に Coalesce を使用できます。

• NULL 値の処理。
• 複数のクエリを 1 つとして実行します。
• 長くて時間のかかる CASE ステートメントを回避します。

CASE ステートメント (または ISNULL 関数) の代わりに使用する場合、Coalesce は多くのパラメーターを受け取りますが、CASE は 2 つしか受け取りません。このアプローチにより、記述するコードの量が減り、作成プロセスが容易になります。

構文は次のとおりです。

 COALESCE(valueOne, valueTwo, valueThree, …, valueX);

SQL サーバーの Coalesce には、同じデータ型の引数、多くのパラメーターを受け入れる、出力として整数を返すために yield 関数によってカスケードされる整数型の引数など、いくつかのプロパティがあります。

こちらもお読みください: 後でブックマークするための究極の SQL チートシート

ただし、Coalesce の使用方法に入る前に、NULL について理解しましょう。

SQL の NULL 値とは何ですか?

SQL の一意のマーカー NULL は、データベース内に値が存在しないことを示します。これは未定義または未知の値と考えることができます。これを空の文字列またはゼロ値として考えるという落とし穴にはまらないようにしてください。それは価値の欠如です。テーブルの列に NULL が出現すると、情報が欠落していることを表します。

実際の使用例では、顧客が ID を提供しなかった場合、電子商取引 Web サイトのデータベース列のデータ列に NULL 値を入力できます。 SQL における Null は一意です。これは、「 特定のオブジェクトを指していない 」ことを意味する他のプログラミング言語とは対照的に、状態です。

SQL の NULL 値は、リレーショナル データベースに大きな影響を与えます。まず、他の内部関数を操作するときに特定の値を除外できます。たとえば、実稼働環境で合計注文のリストを生成できますが、他のものはまだ完了する必要があります。プレースホルダーとして NULL を使用すると、内部 SUM 関数で合計を加算できるようになります。

さらに、AVG 関数を使用して平均を生成する必要がある場合を考慮してください。ゼロ値を使用すると、結果が歪んでしまいます。代わりに、データベースはそのようなフィールドを削除して NULL を使用し、正確な出力を得ることができます。

NULL 値には欠点はありません。これらは、バイトまたはその複数の可変長値とみなされます。データベースに格納されているバイト数を超える場合、データベースはこれらのバイト用のスペースを残すため、通常の値を使用する場合と比較して、データベースがハード ドライブ上でより多くのスペースを占有することになります。

さらに、一部の関数を使用する場合は、NULL を排除するように関数をカスタマイズする必要があります。その結果、SQL プロシージャが長くなります。

COALESCE () による NULL 値の処理

Null 値は、値を取得できるが、その値が何であるべきかは認識されていないことを意味します。フィールドに実際の値を入力するデータを収集するまでは、NULL 値が保証されます。

データベース内の複数のデータ型 (小数、文字列、BLOB、整数など) に NULL 値を使用できますが、数値データを扱う場合は NULL 値を使用しないことをお勧めします。

欠点は、数値に使用する場合、データを操作するコードを開発する際に説明が必要になる可能性があることです。それについては後で詳しく説明します。

COALESCE () を使用して NULL 値を処理するさまざまな方法:

COALESCE () を使用して Null 値を特定の値に置き換える

COALESCE () を使用すると、すべての null 値に対して特定の値を返すことができます。たとえば、「employees」という名前のテーブルに「salary」列があるとします。従業員の給与が振り込まれていない場合、この列には NULL 値が含まれる可能性があります。したがって、計算を行うときは、すべての NULL エントリに対して特定の値 (この場合はゼロ) を使用することが必要になる場合があります。その方法は次のとおりです。

 SELECT COALESCE(salary, 0) AS adjusted_salary
FROM employees; 

COALESCE () を使用して複数のオプションから最初の非 null 値を選択する

場合によっては、式のリスト内の最初の非 NULL 値を操作したい場合があります。このような場合、多くの場合、関連データを含む複数の列があり、それらの非 NULL 値を優先する必要があります。構文は残ります。

 COALESCE (expression1, expression2, …)

実際のケースでは、 preferred_name および full_name 列を含む contacts テーブルがあるとします。また、連絡先の優先名 (可能な場合) またはフルネームを並べてリストを生成することもできます。対処方法は次のとおりです。

 SELECT COALESCE(preferred_name, full_name) AS display_name
FROM contacts.

このテスト ケースでは、 preferred_name が NULL でない場合、それが返されます。それ以外の場合は、 full-name 表示名として返されます。

SQL Coalesce による文字列連結

NULL 値が含まれる場合、文字列を連結するときに SQL で問題が発生する可能性があります。このような場合、望ましくない結果として NULL が返されます。 NULL は望ましい結果ではないので、Coalesce 関数を使用して問題を解決できます。以下に例を示します。

単純な文字列の連結は次のように行われます。

 SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example

コードは以下を返します:

例

こんにちは、ジョン、どこにいるの？

ただし、次に示すように NULL 値を使用する場合は、次のようになります。

 SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example

出力は今です。

例

ヌル

NULL 値を含むすべてのテキスト文字列連結は NULL を返すため、上記の結果は NULL になります。ただし、この問題は、 coalesce () を使用して解決されます。この関数を使用すると、NULL の代わりに空の文字列 (またはスペース) が返されます。たとえば、車の名前とそのメーカーをリストしているとします。これがあなたの質問です。

 SELECT 
car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand
FROM stock

メーカーが NULL の場合は、NULL の代わりに「-」が表示されます。期待される結果は次のとおりです。

車のブランド

アウトランダー、メーカー: —

フライングスパー、メーカー：ベントレー

ロイヤルアスリート、メーカー: —

ロイヤルサルーン、メーカー：クラウン

ご覧のとおり、置換文字列値を挿入するオプションを使用して、NULL の結果が削除されます。

SQL Coalesce 関数とピボット

SQL ピボットは、行を列に変換するために使用される手法です。データを「正規化」形式 (行が多く列が少ない) から「非正規化」形式 (行が少なく列が多い) に転置 (回転) できます。 Coalesce 関数を SQL ピボットで使用すると、ピボットされた結果の NULL 値を処理できます。

SQL で PIVOT 実行すると、行が列に変換されます。結果として得られる列は、一部のデータの集計関数です。いずれの場合でも、特定のセルの集計結果が null になった場合は、` COALESCE ` を使用して null 値をデフォルト値または意味のある表現に置き換えることができます。以下に例を示します。

テーブル sales に、 year 、 quarter 、 revenue という列があり、データをピボットしたいとします。たとえば、列として年が表示され、値として各四半期の収益の合計が表示されます。ただし、一部の四半期には収益データがなく、ピボットされた結果に null 値が表示されます。この場合、 COALESCE を使用して、ピボットされた結果の NULL 値をゼロ (0) に置き換えることができます。

 SELECT
    year,
    COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN revenue END), 0) AS Q1_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN revenue END), 0) AS Q2_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q3' THEN revenue END), 0) AS Q3_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q4' THEN revenue END), 0) AS Q4_Revenue
FROM sales
GROUP BY year; 

スカラーユーザー定義関数とSQL結合関数

スカラー UDF と合体を使用して、NULL 値を処理する複雑なロジックを実行できます。これらの機能を組み合わせると、SQL クエリでより高度なデータ変換と計算を実現できるようになります。この構造を持つテーブル Employees について考えてみましょう。

 CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary INT,
    Bonus INT
);

各従業員の総収入 (給与とボーナス) を計算するとよいでしょう。ただし、欠損値がいくつかあります。この場合、スカラー UDF は給与とボーナスの加算を処理でき、Coalesce は NULL 値を処理します。総収益のスカラー UDF は次のとおりです。

 CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
    DECLARE @totalEarnings INT;
    SET @totalEarnings = @salary + COALESCE(@bonus, 0);
    RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
       Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees; 

SQL Coalesce を使用したデータ検証

データベースを操作するとき、数値を検証する必要がある場合があります。たとえば、テーブル products に列 product_name 、 price 、および discount があるとします。各アイテムの製品名、価格、割引を取得したいとします。ただし、すべての NULL 割引値を 0 として扱いたい場合は、Coalesce 関数が役立ちます。使用方法は次のとおりです。

 SELECT product_name, price, COALESCE(discount, 0) AS discount 
FROM products

SQL 結合と計算列

計算列は、式またはテーブル内の他の列に基づいて計算される仮想列です。計算列はデータベースに物理的に保存されないため、複雑なシナリオや変換を処理するときに結合関数を使用して計算列を利用できます。ここでは実際の使用例を示します。

列「 price 」、「 discount 」、および「 tax_rate 」を含む「 products 」テーブルを考えてみましょう。この場合、割引と税金を適用した後の最終的な製品価格を表す計算列 ` total_price ` を作成したいとします。割引または税金が指定されていない場合 (NULL)、ゼロを使用して計算を続行することをお勧めします。運用に合わせて合体を活用する方法を紹介します。

 CREATE TABLE products(
price DECIMAL(10, 2),
discount DECIMAL(10, 2),
tax_rate DECIMAL(5, 2),
total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1)
);

上記のコードでは、次のことが起こります。

1. total_price 計算列 (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1) として定義されます。
2. price が NULL の場合、 COALESCE(price*discount, 0) 価格は 0 として扱われます。
3. discount が null の場合、 COALESCE(price*discount) 割引が 0 として扱われることが保証され、乗算は計算に影響しません。
4. tax_rate が NULL の場合、 COALESCE(1 + tax_rate, 1) 税金が適用されず、乗算が計算に影響しないことを意味する 0 として扱われることが保証されます。

上記の設定により、値が欠落しているか NULL 値がある場合でも、実際の最終価格を含む計算列 total_price を生成できます。

SQL結合とCASE式

CASE 式を通じて構文的に結合を使用できます。以下に例を示します。

 SELECT
Productname + ‘ ’+ deliverydate productdetails,
dealer,
CASE
WHEN cellphone is NOT NULL Then cellphone
WHEN workphone is NOT NULL Then workphone
ELSE ‘NA’
END
EmergencyContactNumber
FROM
dbo.tb_EmergencyContact

上記の設定では、 CASE クエリは COALESCE 関数と同様です。

さらに、同じクエリ内で COALESCE と CASE 式を使用することも可能です。この 2 つの手法では、NULL 値を処理し、条件付きロジックを同時に適用できます。これを例で説明してみましょう。

product_id 、 product_name 、 price 、および discount を持つテーブル products ある場合を考えてみましょう。製品の中には特別な割引があるものと、そうでないものがあります。製品に割引がある場合は割引価格を表示し、そうでない場合は通常価格を表示する必要があります。

 SELECT 
    product_id,
    product_name,
    price,
    COALESCE(
        CASE
            WHEN discount > 0 THEN price - (price * discount / 100)
            ELSE NULL
        END,
        price
    ) AS discounted_price
FROM products;

上記のコードでは、` CASE ` が ` discount ` が 0 より大きいかどうかをチェックし、割引価格を計算します。それ以外の場合は NULL を返します。 ` COALESCE ` 関数は、` CASE ` と ` price ` からの結果をパラメータとして受け取ります。最初の非 NULL 値を返し、事実上、利用可能な場合は割引価格を、存在しない場合は通常価格を返します。

最後の言葉

この投稿では、データベース クエリで ` COALESCE ` 関数を使用するさまざまな方法を説明しました。指定された順序でパラメータを評価し、最初の非 NULL 値を返すことにより、Coalesce 関数はクエリを簡素化し、クエリを効率化します。

Coalesce は、NULL 値、文字列の連結、データのピボット、検証、または計算列の操作を処理する場合でも、多用途の関数です。結合機能を習得することで、開発者は欠落しているデータをナビゲートし、エラーのないデータベース設計を作成できます。テクニックをマスターすることを忘れないでください。さらに詳しい練習が必要になる場合があります。

SQL で外部キー制約を作成する方法を確認できるようになりました。