CAST と COALESCE ― 型を変える・NULLを埋める

導入

SQL には地味だけど大事な落とし穴があります。「整数どうしの割り算は、答えも整数に切り捨てられる」というものです。7 / 2 が 3.5 ではなく 3 になる。これを避けたり、NULL を別の値で埋めたりするのが、この章の締めくくりです。

説明

CAST ― 型を明示的に変える。CAST(値 AS 型) で、値を別の型として扱わせます。整数の割り算を小数にしたいときは、片方を REAL（実数）にキャストします。

flowchart LR
    A["7 / 2<br/>（整数 ÷ 整数）"] --> B["3（切り捨て）"]
    C["CAST(7 AS REAL) / 2<br/>（実数 ÷ 整数）"] --> D["3.5"]

SELECT 7 / 2 AS 整数割り算, CAST(7 AS REAL) / 2 AS 小数割り算;

主な型は INTEGER（整数）・REAL（小数）・TEXT（文字列）です。「数値を文字として扱いたい」「文字の ‘100’ を数として足したい」ときにも CAST を使います。

COALESCE ― NULL を別の値で埋める。第1章の NULL 回で少し触れました。COALESCE(a, b) は「a が NULL でなければ a、NULL なら b」を返します。「未設定なら 0 とみなす」「空欄なら『未登録』と表示する」の定番です。

SELECT name, COALESCE(city, '未登録') AS city FROM users;

引数は3つ以上も並べられ、「左から見て最初の非NULL」を返します。集計や計算に NULL が混ざると結果全体が NULL になりがちなので、COALESCE で埋めてから計算するのは実務の常套手段です。

やってみよう

初期表示のクエリを実行し、7 / 2 が 3、CAST(7 AS REAL) / 2 が 3.5 になる違いを確認しましょう。整数割り算の切り捨ては、集計でうっかりハマる定番です。体で覚えておきましょう。

演習

products の平均価格を、小数まできちんと出してください。AVG(price) はうまく出ますが、ここでは練習として CAST(SUM(price) AS REAL) / COUNT(*) の形で平均を計算し、平均 という別名を付けてください。