特別な値 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 を使用する。
| 分類 | aの値 | 結果 |
| a IS NULL | 10 でも "abc" でも | FALSE |
| a IS NOT NULL | 10 でも " "abc" でも | TRUE |
| a IS NULL | NULL | TRUE |
| a IS NOT NULL | NULL | FALSE |
| a = NULL | 10 でも NULLでも | UNKNOWN(レコードは選択されない) |
| a = 10 でも a != 10でも | NULL | UNKNOWN(レコードは選択されない) |
10 = ANY( a, 10, 11, 12)
※ ALL 条件でも同じ | NULL | UNKNOWN※ |
※副問い合わせ内で存在しない or NULL を戻すレコードが1つでも存在した場合にはUNKNOWNとなる*1
・条件でNULLが使用され、結果がNULLの値に依存する場合 は UNKNOWN になる。
例) ( 1 = 1 OR 10=NULL )・・・ TRUE
( 1 = 1 AND 10=NULL ) NULL ・・・クエリは UNKNWON |
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 の問題。
忘れた頃に、また思い出させるような事が続く問題です。
入門者がコレと対峙すると、簡単に脱出できません。
売上累計と売上明細に関する仮想問題
を参考にしてください。
印刷表示