[sqlite3] NULLの扱いとCOALESCE関数

[SQL] NULLの扱いとCOALESCE関数

今回はSQLにおける NULL の扱いと、それを便利に処理する COALESCE（コレス）関数について解説する。

SQLにおいて NULL は「値が存在しない」または「不明」であることを表す特殊なマーカーである。 数値の 0 や空文字 '' とは明確に区別され、計算や比較において予期せぬ挙動を引き起こすことがある。

この NULL を適切にハンドリングするために必須となるのが COALESCE 関数である。

準備

本サイトではpythonのライブラリであるsqlite3とipython-sqlを利用して、Jupyter Notebook上でSQLを実行・解説する。 また、データフレームの表示には Polars を利用する。

github

• githubのjupyter notebook形式のファイルはこちら

google colaboratory

• google colaboratory で実行する場合はこちら

筆者の環境

筆者の環境である。

!sw_vers

ProductName:		macOS
ProductVersion:		15.5
BuildVersion:		24F74

!python -V

Python 3.12.12

必要なライブラリを読み込む。

import sqlite3
import polars as pl

%load_ext sql
%config SqlMagic.feedback = True

%config SqlMagic.style = '_DEPRECATED_DEFAULT'

データの準備

サンプルとして、商品テーブル Products を作成する。 一部の商品には price（価格）や description（説明）が設定されていない（NULL）ものとする。

import os

if os.path.exists("data.db"):
    os.remove("data.db")

os.system("touch data.db")
os.system("chmod 664 data.db")

# データベース接続
%sql sqlite:///data.db

'Connected: @data.db'

%%sql
CREATE TABLE Products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price INTEGER,
    description TEXT
);

INSERT INTO Products (name, price, description) VALUES
    ('商品A', 1000, '素晴らしい商品です'),
    ('商品B', NULL, '価格未定'),
    ('商品C', 500, NULL),
    ('商品D', NULL, NULL);

 * sqlite:///data.db
Done.
4 rows affected.





[]

%%sql
SELECT * FROM Products;

 * sqlite:///data.db
Done.

NULLの問題点

例えば、価格に消費税（10%）を加えた金額を計算したいとする。 通常の計算式 price * 1.1 を実行するとどうなるか。

query = """
SELECT
    name,
    price,
    price * 1.1 as price_with_tax
FROM
    Products;
"""

with sqlite3.connect("data.db") as conn:
    df = pl.read_database(query, connection=conn)
    display(df)

shape: (4, 3)

price が NULL の行は、計算結果も NULL になってしまう。 SQLでは基本的に NULLを含む計算の結果はすべてNULLになる というルールがあるためである。

COALESCE関数の活用

COALESCE(引数1, 引数2, ...) は、引数の中から最初に現れるNULLでない値 を返す関数である。

これを使って、「価格がNULLの場合は0として扱う」、「説明がNULLの場合は’不明’と表示する」といった処理を行ってみる。

query = """
SELECT
    name,
    -- 価格がNULLなら0を返す
    COALESCE(price, 0) as safe_price,
    -- safe_priceを使って計算
    COALESCE(price, 0) * 1.1 as price_with_tax,
    -- 説明がNULLなら'不明'を返す
    COALESCE(description, '不明') as safe_description
FROM
    Products;
"""

with sqlite3.connect("data.db") as conn:
    df = pl.read_database(query, connection=conn)
    display(df)

shape: (4, 4)

解説

• COALESCE(price, 0): price があればその値を、なければ 0 を返す。
• COALESCE(description, '不明'): description があればその値を、なければ '不明' を返す。

これにより、計算結果が NULL になるのを防いだり、表示上の欠損を埋めたりすることができる。

まとめ

• SQLの計算において、NULL は結果を NULL にしてしまう（感染する）。
• COALESCE 関数を使うことで、NULL を特定の値（デフォルト値）に置き換えることができる。

データの集計やレポート作成時には、予期せぬ NULL による計算ミスを防ぐため、積極的に COALESCE を活用しよう。