集約関数の対象行が0件の場合や対象列がNULLを含む場合、結果として0ではなくNULLが返されることがあるので注意しましょう。
SQL標準での規定
SQL:2011では、集約関数が返す結果について以下のように規定されています。
- COUNT(*)は、集約結果内の行数を返す
- 以下の行は集約関数の対象とならない
- DISTINCT指定時の重複行
- 評価結果がNULLとなる行
- 対象となる行が存在しない場合、COUNTの結果はゼロ、その他の集約関数の結果はNULLとなる
つまり、以下のような整理になります。
| 関数 | 0行の場合 | 対象の列が全てNULLの場合 | 備考 |
|---|---|---|---|
| COUNT(*) | 0 | 行数 | 全ての列がNULLの行であっても1行としてカウントされる |
| COUNT(column) | 0 | 0 | |
| MAX(column) | NULL | NULL | |
| MIN(column) | NULL | NULL | |
| SUM(column) | NULL | NULL | |
| AVG(column) | NULL | NULL | NULLの列は分母の件数としてカウントされない |
主要なRDBMSのほとんどはこの規定にきちんと従っているようです。
対象列がNULLで除外されたものがある場合、SQLSTATE 01003が返されます。これもSQL標準の仕様ですが、MySQL 5.7は対応していないようです。
NULLを返さないでほしい場合
COALESCE、NVL、IFNULL等を用いてNULLを0に置換してください。
SELECT COALESCE(SUM(colname), 0) FROM tablename
参考資料
- Firebird Null Guide