Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

SQLのcoalesceについてまとめています。

coalesceは、NULLの代替値を返す関数

  1. mysql> SELECT COALESCE(NULL,1);
  2. -> 1
  3. mysql> SELECT COALESCE(NULL,NULL,NULL);
  4. -> NULL

coalenceは指定した値のうち、NULLではない最初の値を返す関数で、指定カラムがNULLでも、NULLを返したくない場合などに使います。

  1. SELECT product_id, list_price, min_price,
  2. COALESCE(0.9*list_price, min_price, 5) "Sale"
  3. FROM product_information
  4. WHERE supplier_id = 102050
  5. ORDER BY product_id;
  6.  
  7. PRODUCT_ID LIST_PRICE MIN_PRICE Sale
  8. ---------- ---------- ---------- ----------
  9. 1769 48 43.2
  10. 1770 73 73
  11. 2378 305 247 274.5
  12. 2382 850 731 765
  13. 3355 5

 

なお、データベースによってはcoalesceの簡易版のNVL(oracle)やNULLIF(PostgreSQL)といった関数が用意されています。

【関連記事】
SQL coalesce 値がNULLの場合の代替値を返す 簡易版のNVLやNULLIFが使えるDBもあり

coalesceの使い方

coalesceでサブクエリを使うには、サブクエリに括弧をつける

coaleseの引数にはサブクエリを使うことが可能です。そのまま記述するとsyntaxエラーになるので、サブクエリ部分を括弧でくくりましょう。

  1. mysql> select coalesce( select NULL, 0 ) as value;
  2. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select NULL, 0 ) as value' at line 1
  3. #サブクエリにカッコをつけると、syntaxエラーが解消される
  4. mysql> select coalesce( (select NULL), 0 ) as value;
  5. +-------+
  6. | value |
  7. +-------+
  8. | 0 |
  9. +-------+
  10. 1 row in set (0.00 sec)

関連)SQL関数「COALESCE」でサブクエリを使うにはひと工夫が必要 – ソフラボの技術ブログ

coaleceはcase式で置き換えが可能

coaleceはcase式による置き換えが可能です。下記は、first_name(姓名の名)がNULLの場合のみ”no_data”を返すSQLです。

  1. mysql> select * from employees_copy limit 10;
  2. +--------+------------+--------------+-----------+--------+------------+
  3. | emp_no | birth_date | first_name | last_name | gender | hire_date |
  4. +--------+------------+--------------+-----------+--------+------------+
  5. | 10001 | 1953-09-02 | | Facello | M | 1986-06-26 |
  6. | 10002 | 1964-06-02 | | Simmel | F | 1985-11-21 |
  7. | 10003 | 1959-12-03 | | Bamford | M | 1986-08-28 |
  8. | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
  9. | 10005 | 1955-01-21 | NULL | Maliniak | M | 1989-09-12 |
  10. | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
  11. | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
  12. | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
  13. | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
  14. | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
  15. +--------+------------+--------------+-----------+--------+------------+
  16. 10 rows in set (0.05 sec)
  17.  
  18. #NULLの項目だけno dataに変換される
  19. mysql> select coalesce(first_name, "no data") from employees_copy limit 10;
  20. +---------------------------------+
  21. | coalesce(first_name, "no data") |
  22. +---------------------------------+
  23. | |
  24. | |
  25. | |
  26. | Chirstian |
  27. | no data |
  28. | Anneke |
  29. | Tzvetan |
  30. | Saniya |
  31. | Sumant |
  32. | Duangkaew |
  33. +---------------------------------+
  34. 10 rows in set (0.00 sec)

空白や、1文字目が空白のものはそのまま出力され、NULLの箇所のみno dataという表示に置き換わっています。

case式で置き換えるとこうなります。

  1. mysql>SELECT
  2. CASE
  3. WHEN first_name IS NULL THEN "no data"
  4. ELSE first_name
  5. END
  6. FROM employees_copy limit 10;
  7. +-----------------------------------------------------------------+
  8. | case when first_name is null then "no data" else first_name end |
  9. +-----------------------------------------------------------------+
  10. | |
  11. | |
  12. | |
  13. | Chirstian |
  14. | no data |
  15. | Anneke |
  16. | Tzvetan |
  17. | Saniya |
  18. | Sumant |
  19. | Duangkaew |
  20. +-----------------------------------------------------------------+

【関連記事】
SQLのCASE式サンプル集 order byやgroup byとの組み合わせもバッチリ 

まとめ

ポテパンダの一言メモ
  • coalesceは、NULL値の代替を返す関数
  • coalesceの引数にサブクエリを使うときは、サブクエリ部分を括弧でくくる
  • coalsceはcase式で置き換えが可能

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。