特別な値 NULL 値
オラクルにおける NULL とは、C言語などの NULL STRING '\0' CHAR(0) とは別の存在である。
また、数学の空集合(a set null) と比較すると宇宙の特異点のようなブラックホールとも思える強力なパワーで特別な振る舞いをする。
それを知らないでおくと、ブラックホールに飲み込まれて大切なデータを失う時限式の不具合を抱えることになりかねない。
NULL と論理演算
| x | y | x AND y | x OR y |
| TRUE | NULL | NULL | TRUE |
| FALSE | NULL | FALSE | NULL |
| NULL | NULL | NULL |
NOT NULL も NULL になる。
比較条件 と NULL
NULL はデータの欠落をあらわす、任意の値や別の NULL と等号や不等号は成立しない。
直接比較するには IS NULL ／ IS NOT NULL を使用する。
| val の値 | 条件式 | 検索の結果 |
| 10 | val IS NULL | FALSE |
| 'abc' | FALSE |
| 10 | val IS NOT NULL | TRUE |
| 'abc' | TRUE |
| NULL | val IS NULL | TRUE |
| val IS NOT NULL | FALSE |
| NULL | val = NULL | UNKNOWN
(TRUE でも FALSEで もない） |
| NOT val = NULL |
| NULL | val = 10 | UNKNOWN
(TRUE でも FALSEで もない） |
| NOT val = 10 |
| val <> 10 |
| NOT val <> 10 |
| val = 'abc' |
| NOT val = 'abc' |
| NULL | val IN ( 1, 2, 3) |
| val NOT IN ( 1, 2, 3) |
| val IN ( NULL, 2, 3) |
| val NOT IN ( NULL, 2, 3) |
| val IN ( 'a', 'b', 'c') |
| val NOT IN ( 'a', 'b', 'c') |
| val IN ( NULL, 'b', 'c') |
| val NOT IN ( NULL, 'b', 'c') |
NULL のソート ORDER BY NULLS 〜
Oracle 8.1.6 以上であれば、NULL の並べ替えのために NVL,MIN 関数を使う必要がなくなっている。
NULL はデフォルトの昇順ソートでは最後、降順ソートでは最初に並び替えられる。(≒無限大)
ORDER BY のオプション指定により、その並び順を変更することができる。
- NULLS FIRST
- NULL 値を順序の最初にするソートを行なう
- NULLS LAST
- NULL 値を順序の最後にするソートを行なう
例) ORDER BY ORDER_NO ASC NULLS FIRST
NULL の基本的な振る舞い
算術演算 と NULL
NULL を含む算術式はすべて NULL となる。
例） 1 + NULL ⇒ NULL
NULL / 0 ⇒ NULL ※ 0除算エラーにならない。
連結演算(||) と NULL
連結演算子は一方だけが NULL である場合は結果は NULL とならない。
2つのオペランドが NULL の場合には NULL となる。
例）SELECT FIRST_NAME || MIDDLE_NAME || LAST_NAME FROM "名簿"
⇒ '鈴木' + NULL + 'イチロウ' ⇒ 鈴木イチロウ
単一行ファンクション と NULL
NULL を引数とする、ほぼすべての単一行ファンクション(旧スカラーファンクション)は NULL が戻される。参考: 長さ０の文字の長さはゼロ？
但し、REPLACE、NVL、NVL2 、NULLIF、LNNVL、DECODE、COALESCE および CONCATを除く(他にもあるかもしれません)。
SELECT NVL(LENGTH(NULL),-1) LEN FROM DUAL ;
LEN
----------
-1
集計ファンクション と NULL
NULL を引数とする、ほぼすべての集計ファンクションは、NULL の行を無視(存在しないものと)する。
COUNT(*) は NULL も含む全件数を戻す。COUNT(カラム名)は、NULL を含まない件数を戻す。
SQL> COLUMN NUM NULL '<NULL>'
SQL> SELECT NUM FROM NUM_TEST ;
NUM
----------
10
<NULL>
20
<NULL>
30
<NULL>
40
<NULL>
8行が選択されました。
SQL> SELECT
2 SUM(NUM) "合計",
3 AVG(NUM) "平均(NUM)", AVG(NVL(NUM, 0)) "平均(*)",
4 COUNT(NUM) "件数(NUM)", COUNT(*) "件数(*)"
5 FROM NUM_TEST ;
合計 平均(NUM) 平均(*) 件数(NUM) 件数(*)
---------- ---------- ---------- ---------- ----------
100 25 12.5 4 8
⇒ SQL*Plus の NULL 表示
副問い合わせのコーディングミスによる UPDATE での不具合
更新問い合わせに関する NULL の問題。
忘れた頃に、また思い出させるような事が続く問題です。
入門者がコレと対峙すると、簡単に脱出できません。
売上累計と売上明細に関する仮想問題
を参考にしてください。
