一度私的にまとめておこう、という判断から両DBのnullと空文字の扱い方について実験・整理する。
結論は下記のようになる。
- SQL Serverはnullと空文字は別物扱い。
- Oracleはnullと空文字はnull扱い。
検証環境
SQL Server 2005とOracle 10g環境で動作確認を行った。主キーでない列―各DBでvarcharとvarchar2―にnullと空文字を挿入し、SELECTをさまざまな条件で発行したときどうなるかを調べる。
各行データは、非null、null、空文字、空文字、nullの順にINSERTを発行した。非null行をのぞき各行データの呼称は、null1、blank1、blank2、null2とする。主キー列にも同様の文字列が入れてある。この呼称はオマケでやったORDER BY実験のときに使うので、主旨のnull・空文字実験には関係がない。
検証結果
SELECT * FROM HOGE_TABLEまでは皆共通。WHERE以下の条件のみ表の第一列に記入されている。表の内容にSELECTの結果を記している。なお、表下段2つについてはデータの表示順序を示している。
条件 | SQL Server | Oracle |
---|---|---|
HOGE_NAME = '' | 空文字 | 0件 |
HOGE_NAME IS NULL | null | null・空文字 |
HOGE_NAME IS NOT NULL | null以外の行 | null・空文字以外の行 |
HOGE_NAME LIKE '%' | null以外の行 | null・空文字以外の行 |
(条件無)ORDER BY HOGE_NAME | null1,null2,blank1,blank2,それ以外の順 | それ以外,null2,bkank2,null1,blank1 |
(条件無) | (主キー順)blank1,blank2,null1,null2 | (?順)それ以外,null1,blank1,blank2,null2 |
・・・とまぁ、最初に書いたように「SQL Serverはnullと空文字を別扱い、Oracleは同一視」という結果になりました*1。正確には、Oracleは空文字もnullも内部的にnullで保存してます。JDBC経由で確認すると、SQL Serverはnullと空文字別扱いするけど、Oracleはnullしか返しません。
参考:NULL値:http://biz.rivus.jp/word_null.html なんかゴチャゴチャ書いてあるがnullと空文字は同一視、という認識で良いらしい
謎なのはORDER BY。SQL Serverは下記の規則なのは読み取れる。
- 列が指定されたとき、null、空文字、それ以外、の順
- 列が指定されないとき、主キー列で自動的に整列
が、Oracleはイマイチ良くわからない結果に・・・と思ったけど
NULLの特性:http://biz.rivus.jp/technote507012.html
NULL はデフォルトの昇順ソートでは最後、降順ソートでは最初に並び替えられる。(≒無限大)
で、NULLS FIRST、NULLS LASTを使うと順序の最初or最後に出来るとのこと。nullが最初or最後に来るってだけでSQL Serverのように勝手に主キーで整列、なんてことにはなりませんでした。
*1:やべ、昨日のコメント間違ってる