MochiuWiki : SUSE, EC, PCB

「MySQL - COALESCE関数」の版間の差分

📢 Webサイト閉鎖と移転のお知らせ
このWebサイトは2026年9月に閉鎖いたします。
新しい記事は移転先で追加しております。（旧サイトでは記事を追加しておりません）

ビジュアルウィキテキスト
ページの作成:「== 概要 == COALESCE関数は、引数として渡された値の中から最初の非NULL値を返す関数である。<br> <br> この関数はNULLをデフォルト値に置き換えることができるため、複数の列から最初の有効な値を取得あるいはレポート作成時にNULLを見やすい表示に変換することができる。<br> <br> <u>※注意</u><br> <u>全ての引数がNULLの場合は、NULLを返す。</u><br> <u>引数の…」
 
編集の要約なし
 
(同じ利用者による、間の1版が非表示)
1行目: 1行目:
== 概要 ==
== 概要 ==
COALESCE関数は、引数として渡された値の中から最初の非NULL値を返す関数である。<br>
COALESCE関数は、引数として渡された値の中から最初の非NULL値を返す関数である。<br>
COALESCE関数はSQL標準の関数であり、主要なRDBMSで同じように使用できるため、MySQL、SQL Server、PostgreSQL、Oracle等で共通して使用できる。<br>
<br>
<br>
この関数はNULLをデフォルト値に置き換えることができるため、複数の列から最初の有効な値を取得あるいはレポート作成時にNULLを見やすい表示に変換することができる。<br>
この関数はNULLをデフォルト値に置き換えることができるため、複数の列から最初の有効な値を取得あるいはレポート作成時にNULLを見やすい表示に変換することができる。<br>
11行目: 12行目:
== 基本構文 ==
== 基本構文 ==
  <syntaxhighlight lang="mysql">
  <syntaxhighlight lang="mysql">
  COALESCE(value1, value2, value3, ...)
  COALESCE(<値1>, <値2>, <値3>, ...)
  </syntaxhighlight>
  </syntaxhighlight>
<br><br>
<br><br>
35行目: 36行目:
  FROM customers;
  FROM customers;
  </syntaxhighlight>
  </syntaxhighlight>
<br><br>
== 動作の詳細 ==
COALESCE関数は、引数を左から順に評価し、最初の非NULL値を見つけた時点でその値を返す。<br>
<br>
==== 短絡評価 (Short-Circuit Evaluation) ====
COALESCE関数は、短絡評価を行う。<br>
<br>
短絡評価とは、最初の非NULL値が見つかった時点で、残りの引数の評価を中止する動作である。<br>
これにより、計算コストの高い式を後ろの引数に配置することで、パフォーマンスを向上させることができる。<br>
<br>
<syntaxhighlight lang="mysql">
-- primary_emailが非NULLの場合、サブクエリは実行されない
SELECT COALESCE(
    primary_email,
    (SELECT email FROM email_backup WHERE user_id = users.id LIMIT 1),
    'no-email@example.com'
) AS email
FROM users;
</syntaxhighlight>
<br>
この例では、<code>primary_email</code> が非NULLの場合、サブクエリは実行されないため、パフォーマンスが向上する。<br>
<br>
==== インデックスへの影響 ====
COALESCE関数を <code>WHERE</code> 句で使用する場合、インデックスの使用に影響を与える可能性がある。<br>
<br>
<syntaxhighlight lang="mysql">
-- インデックスが使用されない可能性がある
SELECT * FROM users
WHERE COALESCE(status, 'unknown') = 'active';
-- インデックスを使用する代替方法
SELECT * FROM users
WHERE status = 'active' OR (status IS NULL AND 'unknown' = 'active');
</syntaxhighlight>
<br>
列に関数を適用すると、インデックスが無効になる場合がある。<br>
パフォーマンスが重要な場合は、<code>OR</code> 条件を使用してインデックスを活用することを検討する必要がある。<br>
<br><br>
== 型変換の動作 ==
COALESCE関数は、引数の型が異なる場合、暗黙的な型変換を行う。<br>
<br>
==== 型の優先順位 ====
MySQLは、以下の優先順位で型を決定する。<br>
<br>
* STRING (文字列型)
* REAL (浮動小数点型)
* INTEGER (整数型)
<br>
優先順位が高い型に合わせて、全ての引数が変換される。<br>
<br>
<syntaxhighlight lang="mysql">
-- 文字列型が優先される
SELECT COALESCE(100, 200, '300');  -- 結果: '100' (文字列として返される)
-- 浮動小数点型が優先される
SELECT COALESCE(100, 200.5, 300);  -- 結果: 100.0 (浮動小数点型として返される)
-- 整数型同士
SELECT COALESCE(100, 200, 300);    -- 結果: 100 (整数型として返される)
</syntaxhighlight>
<br>
==== 日付型と文字列型の変換 ====
日付型と文字列型を混在させる場合、文字列型が優先される。<br>
<br>
<syntaxhighlight lang="mysql">
-- 日付型が文字列型に変換される
SELECT COALESCE(created_at, '日付なし') AS created_date
FROM posts;
-- 結果例: '2024-01-15 10:30:00' または '日付なし'
</syntaxhighlight>
<br>
==== 型変換に関する注意点 ====
型の不一致は、予期しない結果を引き起こす可能性がある。<br>
<br>
<syntaxhighlight lang="mysql">
-- 数値と文字列の比較
SELECT COALESCE(NULL, 0) = '';    -- 結果: 1 (TRUE)
SELECT COALESCE(NULL, '') = 0;    -- 結果: 1 (TRUE)
-- これは、空文字列が数値の0に変換されるため
</syntaxhighlight>
<br>
明示的な型変換を使用することで、予期しない動作を防ぐことができる。<br>
<br>
<syntaxhighlight lang="mysql">
-- 明示的な型変換
SELECT COALESCE(amount, CAST('0' AS DECIMAL(10,2))) AS amount
FROM transactions;
</syntaxhighlight>
<br><br>
== 他の関数との比較 ==
MySQLには、COALESCEに類似した関数がいくつか存在する。<br>
<br>
==== IFNULL関数との比較 ====
<code>IFNULL</code> 関数は、MySQLの独自関数であり、2つの引数のみを受け取る。<br>
<br>
<center>
{| class="wikitable"
|+ COALESCE関数とIFNULL関数の比較
! 特徴 !! COALESCE !! IFNULL
|-
| SQL標準 || 〇 || × (MySQL独自)
|-
| 引数の数 || 2つ以上 || 2つのみ
|-
| パフォーマンス || やや遅い || やや速い
|-
| 移植性 || 高い || 低い
|}
</center>
<br>
<syntaxhighlight lang="mysql">
-- COALESCE関数
SELECT COALESCE(col1, col2, col3, 'デフォルト') FROM table1;
-- IFNULL関数 (2つの引数のみ)
SELECT IFNULL(col1, 'デフォルト') FROM table1;
-- 複数の列をチェックする場合は、ネストが必要
SELECT IFNULL(col1, IFNULL(col2, IFNULL(col3, 'デフォルト'))) FROM table1;
</syntaxhighlight>
<br>
IFNULL関数は、単純な2つの値の比較では高速だが、3つ以上の値をチェックする場合は、COALESCE関数の方が可読性が高い。<br>
<br>
==== CASE文との比較 ====
<code>CASE</code> 文を使用することでも、COALESCEと同様の動作を実現できる。<br>
<br>
<syntaxhighlight lang="mysql">
-- COALESCE関数
SELECT COALESCE(status, 'unknown') AS status FROM users;
-- CASE文での同等の処理
SELECT
    CASE
      WHEN status IS NOT NULL THEN status
      ELSE 'unknown'
    END AS status
FROM users;
-- 複数の値をチェック
SELECT COALESCE(col1, col2, col3, 'デフォルト') FROM table1;
-- CASE文での同等の処理
SELECT
    CASE
      WHEN col1 IS NOT NULL THEN col1
      WHEN col2 IS NOT NULL THEN col2
      WHEN col3 IS NOT NULL THEN col3
      ELSE 'デフォルト'
    END
FROM table1;
</syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ COALESCE関数とCASE文の比較
! 特徴 !! COALESCE !! CASE
|-
| 簡潔性 || 簡潔 || 冗長
|-
| 可読性 || 高い (NULL処理の場合) || やや低い
|-
| 柔軟性 || 低い (NULL処理のみ) || 高い (複雑な条件も可)
|-
| パフォーマンス || 同等 || 同等
|}
</center>
<br>
COALESCE関数は、NULL値の処理に特化しているため、シンプルで可読性が高い。<br>
一方、CASE文は、NULL以外の条件も処理できるため、より柔軟である。<br>
<br>
==== NULLIF関数との組み合わせ ====
<code>NULLIF</code> 関数は、2つの値が等しい場合にNULLを返す関数である。<br>
<br>
NULLIF関数とCOALESCE関数を組み合わせることにより、特定の値をデフォルト値に置き換えることができる。<br>
<br>
<syntaxhighlight lang="mysql">
-- NULLIF関数の基本構文
NULLIF(<値1>, <値2>)  -- 値1と値2が等しい場合はNULLを返し、そうでなければ値1を返す
-- 例: 空文字列をNULLに変換してからデフォルト値に置き換え
SELECT COALESCE(NULLIF(email, ''), 'no-email@example.com') AS email
FROM users;
-- 例: 0を'なし'に置き換え
SELECT COALESCE(NULLIF(count, 0), '無し') AS count_display
FROM statistics;
</syntaxhighlight>
<br>
この組み合わせにより、NULL値だけでなく、特定の値 (空文字列、0等) もデフォルト値に置き換えることができる。<br>
<br><br>
== 実践的な使用パターン ==
COALESCE関数は、様々なSQL文で使用できる。<br>
<br>
==== JOINとの組み合わせ ====
LEFT JOIN や RIGHT JOINでは、結合できなかった行にNULLが設定される。<br>
COALESCE関数を使用することにより、NULLをデフォルト値に置き換えることができる。<br>
<br>
以下の例では、注文がないユーザに対して、<code>order_count</code> と <code>total_amount</code> を0に設定している。<br>
<br>
<syntaxhighlight lang="mysql">
-- LEFT JOINでのNULL処理
SELECT
    u.name,
    COALESCE(o.order_count, 0) AS order_count,
    COALESCE(o.total_amount, 0.00) AS total_amount
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;
</syntaxhighlight>
<br>
==== 集計関数との組み合わせ ====
集計関数は、NULL値を無視するが、全ての値がNULLの場合はNULLを返す。<br>
COALESCE関数を使用することで、デフォルト値を設定できる。<br>
<br>
<syntaxhighlight lang="mysql">
-- 全ての値がNULLの場合のデフォルト値
SELECT
    category,
    COALESCE(SUM(amount), 0) AS total_amount,
    COALESCE(AVG(price), 0.00) AS avg_price,
    COALESCE(MAX(quantity), 0) AS max_quantity
FROM products
GROUP BY category;
</syntaxhighlight>
<br>
==== サブクエリでの使用例 ====
サブクエリの結果がNULLの場合に、デフォルト値を設定できる。<br>
<br>
以下の例では、プライマリメールアドレス、任意のメールアドレス、デフォルトメールアドレスの順に取得を試みる。<br>
<br>
<syntaxhighlight lang="mysql">
-- サブクエリでの使用
SELECT
    id,
    name,
    COALESCE(
      (SELECT email FROM user_emails WHERE user_id = users.id AND is_primary = 1),
      (SELECT email FROM user_emails WHERE user_id = users.id LIMIT 1),
      'no-email@example.com'
    ) AS email
FROM users;
</syntaxhighlight>
<br>
==== UPDATE文での使用例 ====
UPDATE文でCOALESCE関数を使用することで、NULL値を保持しつつ、非NULL値のみを更新できる。<br>
<br>
<syntaxhighlight lang="mysql">
-- NULL値の場合のみデフォルト値を設定
UPDATE users
SET email = COALESCE(email, 'no-email@example.com')
WHERE email IS NULL;
-- 複数の列を同時に処理
UPDATE users
SET
    phone = COALESCE(phone, 'N/A'),
    address = COALESCE(address, '住所不明')
WHERE phone IS NULL OR address IS NULL;
</syntaxhighlight>
<br>
==== INSERT文での使用例 ====
INSERT文でCOALESCE関数を使用することにより、NULL値をデフォルト値に置き換えて挿入できる。<br>
<br>
<syntaxhighlight lang="mysql">
-- SELECTからINSERTする際のNULL処理
INSERT INTO summary_table (user_id, total_orders, total_amount)
SELECT
    user_id,
    COALESCE(COUNT(*), 0),
    COALESCE(SUM(amount), 0.00)
FROM orders
GROUP BY user_id;
-- 別のテーブルからデータをコピーする際のNULL処理
INSERT INTO new_users (name, email, status)
SELECT
    name,
    COALESCE(email, 'no-email@example.com'),
    COALESCE(status, 'pending')
FROM temp_users;
</syntaxhighlight>
<br>
==== GROUP BYでの使用 ====
<code>GROUP BY</code> 句と組み合わせることにより、NULL値を特定のグループとして扱うことができる。<br>
<br>
<syntaxhighlight lang="mysql">
-- NULL値を'未分類'として集計
SELECT
    COALESCE(category, '未分類') AS category,
    COUNT(*) AS product_count,
    SUM(price) AS total_price
FROM products
GROUP BY COALESCE(category, '未分類')
ORDER BY product_count DESC;
</syntaxhighlight>
<br><br>
== よくある間違いと推奨事項 ==
COALESCE関数を使用する時に、よくある間違いと推奨事項を以下に示す。<br>
<br>
==== 空文字列とNULLの混同 ====
空文字列 ('') と NULL値は異なるものである。<br>
COALESCE関数は、NULL値のみを処理し、空文字列は非NULL値として扱われる。<br>
<br>
<syntaxhighlight lang="mysql">
-- 正しい使用例 (空文字列もNULLとして扱う)
SELECT COALESCE(NULLIF(email, ''), 'no-email@example.com') AS email
FROM users;
-- emailが空文字列の場合、NULLに変換されてからデフォルト値が返される
-- 間違った使用例
SELECT COALESCE(email, 'no-email@example.com') AS email
FROM users;
-- emailが空文字列 ('') の場合、空文字列がそのまま返される
</syntaxhighlight>
<br>
==== インデックスを無効にするパターン ====
WHERE句でCOALESCE関数を列に適用すると、インデックスが使用されない場合がある。<br>
<br>
<syntaxhighlight lang="mysql">
-- 効率的 : インデックスを活用
SELECT * FROM users
WHERE status = 'active';
-- 効率的 : NULL値も含める場合
SELECT * FROM users
WHERE status = 'active' OR status IS NULL;
-- 非効率 : インデックスが使用されない可能性
SELECT * FROM users
WHERE COALESCE(status, 'unknown') = 'active';
</syntaxhighlight>
<br>
パフォーマンスが重要なクエリでは、インデックスの使用状況を <code>EXPLAIN</code> コマンドで確認することが推奨される。<br>
<br>
==== 過度なネストの回避 ====
複数のCOALESCE関数をネストすることは、可読性を低下させる。<br>
<br>
<syntaxhighlight lang="mysql">
-- 推奨 : フラットな構造
SELECT COALESCE(col1, col2, col3, col4, 'デフォルト') FROM table1;
-- 避けるべき例 : 過度なネスト
SELECT COALESCE(
    COALESCE(col1, col2),
    COALESCE(col3, col4),
    'デフォルト'
) FROM table1;
</syntaxhighlight>
<br>
==== デフォルト値の型に注意 ====
デフォルト値の型は、他の引数の型と一致させる必要がある。<br>
<br>
<syntaxhighlight lang="mysql">
-- 推奨 : 型を統一
SELECT COALESCE(price, 0.00) AS price FROM products;
-- または、明示的に文字列型に変換
SELECT COALESCE(CAST(price AS CHAR), '不明') AS price FROM products;
-- 問題がある例: 型の不一致
SELECT COALESCE(price, '不明') AS price FROM products;
-- priceが数値型の場合、'不明'は文字列として扱われ、全体が文字列型になる
</syntaxhighlight>
<br>
==== パフォーマンス最適化の推奨事項 ====
COALESCE関数のパフォーマンスを最適化するための推奨事項を以下に示す。<br>
<br>
* 計算コストの低い引数を先に配置する。(短絡評価を活用)
* WHERE句では、可能な限りCOALESCE関数を使用せず、OR条件を使用する。
* 頻繁にアクセスされるクエリでは、NULL値を事前にデフォルト値で更新することを検討する。
* サブクエリを引数に含める場合は、短絡評価を考慮して後ろに配置する。
<br>
<syntaxhighlight lang="mysql">
-- 最適化例 : 短絡評価を活用
SELECT COALESCE(
    cached_value,      -- 最も高速
    calculated_value,  -- 中速
    (SELECT expensive_query FROM table1 WHERE id = users.id)  -- 低速
) AS value
FROM users;
</syntaxhighlight>
<br><br>
== IFNULL関数 ==
MySQLには、IFNULL関数のような独自の代替関数もある。<br>
ただし、2つの引数のみしか指定できない。<br>
<br>
==== IFNULL関数の基本構文 ====
<syntaxhighlight lang="mysql">
-- MySQLの独自関数
IFNULL(<値1>, <値2>)  -- 2つの引数のみ
-- または
IF(<条件>, <値1>, <値2>)
</syntaxhighlight>
<br>
==== IFNULL関数の使用例 ====
<syntaxhighlight lang="mysql">
-- 基本的な使用例
SELECT IFNULL(phone, 'N/A') AS phone FROM users;
-- COALESCEとの比較
SELECT COALESCE(phone, 'N/A') AS phone FROM users;  -- 同じ結果
-- 複数の列を確認する場合
SELECT IFNULL(phone1, IFNULL(phone2, 'N/A')) AS phone FROM users;
SELECT COALESCE(phone1, phone2, 'N/A') AS phone FROM users;  -- より簡潔
</syntaxhighlight>
<br>
==== IF関数との比較 ====
IF関数は、条件式を評価して、真の場合と偽の場合の値を返す。<br>
<br>
<syntaxhighlight lang="mysql">
-- IF関数の使用例
SELECT IF(status IS NOT NULL, status, 'unknown') AS status FROM users;
-- IFNULL関数での同等の処理
SELECT IFNULL(status, 'unknown') AS status FROM users;
-- COALESCE関数での同等の処理
SELECT COALESCE(status, 'unknown') AS status FROM users;
</syntaxhighlight>
<br>
==== パフォーマンスの比較 ====
単純なNULL処理の場合、IFNULL関数はCOALESCE関数よりもわずかに高速である。<br>
<br>
ただし、実際のアプリケーションでは、この差はほとんど無視できるレベルである。<br>
移植性を重視する場合は、SQL標準のCOALESCE関数を使用することが推奨される。<br>
<br>
<center>
{| class="wikitable"
|+ COALESCE、IFNULL、IF関数の比較
! 関数 !! 引数の数 !! SQL標準 !! パフォーマンス !! 用途
|-
| COALESCE || 2つ以上 || 〇 || 普通 || NULL処理全般
|-
| IFNULL || 2つのみ || × || やや速い || 単純なNULL処理
|-
| IF || 3つ (条件, 真, 偽) || × || 普通 || 条件分岐全般
|}
</center>
<br><br>
<br><br>


「http://mochiuwiki.e2.valueserver.jp/index.php?title=MySQL_-_COALESCE関数」から取得