SQLiteでNULLと空文字が入った列のソート順を思い通りに操る方法
データベースのソート順においてNULLや空文字でハマるケースってけっこう多いですよね。
OracleやSQL Server等の商用データベースではSQL文の最後に NULLS FIRST や NULLS LAST を付けることでNULLを最小として扱うのか最大として扱うのか指定できますが、SQL標準には含まれていませんし、SQLiteでももちろん NULLS FIRST はありません。
でもそんな修飾語がなくともORDER BY句にNULLの評価式を書けばSQLite等でも問題なくNULLの順序は操作できます。
このあたりの解説はちょっと検索すれば見つかりますが、妙に複雑なSQL文を書いているケースや、NULLではない空文字※の扱いについては語られていないことが多いようなので、あらためてまとめておこうかなと記事にしてみました。
前提となるテーブル構造
記事タイトルどおり、今回は（も）SQLite※を想定しています。
■テーブル構造
CREATE TABLE product (
product_name TEXT,
price INTEGER
);シンプルに商品名と価格を持ったテーブルを用意してソート順のテストをしてみます。
NULLが含まれているテーブルのソート順
■データ
INSERT INTO product VALUES('商品A', -1000);
INSERT INTO product VALUES('商品B', 0);
INSERT INTO product VALUES('商品C', 1000);
INSERT INTO product VALUES('商品D', NULL);わかりやすく、ソートしたい順に商品名ABCDと並べてみました。
-1000円の商品なんかあるかい！って思うかもですが、割引クーポンを商品データとして登録する運用もある…のかもしれないｗ
0円についても商品のオプション料金とか。そしてNULLについては価格未定の商品など。とりあえずこじつけですが、マイナスもゼロもNULLも価格として設定される可能性がある、という前提で話を進めます。
そのまま価格でソートした場合
■SQL文
SELECT * FROM product ORDER BY price;■結果
| product_name | price |
|---|---|
| 商品D | (NULL) |
| 商品A | -1000 |
| 商品B | 0 |
| 商品C | 1000 |
気持ちとしては商品A→B→C→Dの順で並べたいのにデフォルトでは NULLS FIRST （NULLが先頭）で動作しているらしく、D→A→B→Cとなってしまいました。
ソートした時にNULLは最後にしたい場合
■SQL文
SELECT * FROM product ORDER BY (price IS NULL),price;■結果
| product_name | price |
|---|---|
| 商品A | -1000 |
| 商品B | 0 |
| 商品C | 1000 |
| 商品D | (NULL) |
やったー！できたー！
…と、ここで解説が終わっているケースが多いのですが、もしかしたら、なぜ動作したのか理解しないまま使っている初心者もいるのではないでしょうか。いや実際ぼく自身、現役を引退してコーディングする量が激減しているのでSQL文の評価式って真が-1なのかな？1なのかな？とふと悩むことがありますｗ これではいけない。
そこで、SQL文をこのように書き換えてみます。
■SQL文
SELECT (price IS NULL) AS FLAG1,* FROM product ORDER BY FLAG1,price;■結果
| FLAG1 | product_name | price |
|---|---|---|
| 0 | 商品A | -1000 |
| 0 | 商品B | 0 |
| 0 | 商品C | 1000 |
| 1 | 商品D | (NULL) |
評価式(price IS NULL)の部分を FLAG1 と命名し、SQL文の結果に評価式の結果も表示されるようにしてみました。0→0→0→1という順に並んでいるのがわかります。
1行目～3行目まではpriceはNULLではないので0（＝偽）となり、4行目はpriceがNULLなので1（＝真）となるわけですね。これでSQLiteの評価式の真(true)は1だとわかりましたｗ
まぁ調べたいだけなら SELECT 1=1 とか SELECT 'A'='A' みたいなSQL文を投げて結果を見れば一目瞭然ですけれども。
逆順ソートでもNULLは最後にしたい場合
■SQL文
SELECT (price IS NULL) AS FLAG1,* FROM product ORDER BY FLAG1,price DESC;■結果
| FLAG1 | product_name | price |
|---|---|---|
| 0 | 商品C | 1000 |
| 0 | 商品B | 0 |
| 0 | 商品A | -1000 |
| 1 | 商品D | (NULL) |
つまり、昇順だろうが降順だろうが、NULLは最後にしたい！というケースですね。
わかりやすいように今回も FLAG1 と命名して結果に表示しました。この評価式の部分をきちんと理解していれば、逆順ソートする際に、あれれ、FLAG1にもDESC付けるんだったかな、priceだけで良いんだったかな、などと悩むこともないと思います。
NULLに加えて空文字も含まれているテーブルのソート順
予想より長くなりましたが、ここまでがよくあるNULLのソート順の話。ここからが空文字を交えた場合の話です。
■データ
INSERT INTO product VALUES('商品A', -1000);
INSERT INTO product VALUES('商品B', 0);
INSERT INTO product VALUES('商品C', 1000);
INSERT INTO product VALUES('商品D', NULL);
INSERT INTO product VALUES('商品E', '');前回の例に価格に空文字を入れた「商品E」が1行加わっています。
ちなみに価格に空文字なんて入らんやろ！そもそも INTEGER やんけ！というツッコミもあろうかと思いますし、ごもっともなのですが、SQLiteだと出来ちゃいます。
SQLiteの型はものすごーくゆるゆるで、挿入されたデータによって自動的に変わるんですね。
それこそINTEGERと一口に言っても128以下の値しか入れないなら1バイトしか使わないし、逆に大きければ最大で8バイト分、つまり922京まで扱うことが出来ます。
更にはINTEGERの項目に文字列を挿入してもエラーにはならず、そのときだけはTEXTとして扱われます。
プログラミング言語としても型がゆるゆるなPHPと合わさるといろんな状況でINTEGER項目に空文字がセットされる可能性が出てきます。ホントはダメなんですけどねｗ でも、運用上そうなってしまった。とりあえずソート順をなんとかしたい、というケースがあるじゃないですか。うん、実際、そういう場面にぼく自身が遭遇したのでこんな記事を書いています。
そのまま価格でソートした場合
■SQL文
SELECT * FROM product ORDER BY price;■結果
| product_name | price |
|---|---|
| 商品D | (NULL) |
| 商品A | -1000 |
| 商品B | 0 |
| 商品C | 1000 |
| 商品E |
さあ、カオスになって参りました。
NULLが先頭なのは前回説明したとおりですが、空文字については最後に表示されます。
それなら、さっきと同様、ORDER BY句にprice IS NULLの評価式を入れれば良いのでは、と考えますよね。
ソートした時にNULLは最後にしたい場合
■SQL文
SELECT (price IS NULL) AS FLAG1,* FROM product ORDER BY FLAG1,price;■結果
| FLAG1 | product_name | price |
|---|---|---|
| 0 | 商品A | -1000 |
| 0 | 商品B | 0 |
| 0 | 商品C | 1000 |
| 1 | 商品E | |
| 1 | 商品D | (NULL) |
でき………てなーい！！
実際、NULLを最後尾にするという要件はクリアしているので、これはこれで良い、というシステムもあるかと思います。
しかし今回は、
- 価格順にソートしたときNULLや空文字は後ろに表示する
- このとき、NULLよりも後ろに空文字を表示する
という条件を加えたいと思います。
ソートした時にNULLよりも空文字を最後にしたい場合
■SQL文
SELECT
(price IS NULL OR price='') AS FLAG1,
(price IS NOT NULL) AS FLAG2,
*
FROM product ORDER BY FLAG1,FLAG2,price;■結果
| FLAG1 | FLAG2 | product_name | price |
|---|---|---|---|
| 0 | 1 | 商品A | -1000 |
| 0 | 1 | 商品B | 0 |
| 0 | 1 | 商品C | 1000 |
| 1 | 0 | 商品D | (NULL) |
| 1 | 1 | 商品E |
今度こそ出来たー！
前回まではFLAG1は(price IS NULL)だけでしたが、ここに空文字も条件に加え、(price IS NULL OR price='')という評価式に変更しています。このようにOR条件も普通に書けるというのがポイント。
そしてそれだけでは「NULLまたは空文字のときFLAG1が1になる」というだけなので、もひとつFLAG2を追加。
こちらは単純に(price IS NOT NULL)なので、「NULLのときに0」「NULL以外なら1」となるわけです。
こうすることで明確に商品D（＝NULL）と商品E（＝空文字）の順位付けをしています。
まとめ
- SQLiteにおいてNULLが入った項目をソートした場合、他の値より先に表示される。
- NULLを最後に表示したい場合は (price IS NULL) 等の評価式をORDER BY句に追加する。
- 空文字を最後に表示したい場合は (price = '') 等の評価式をORDER BY句に追加する。
- NULLと空文字が混在し、かつ順位付けしたい場合は2つの評価式をORDER BY句に追加する。
といったところでしょうか。
最後の部分は蛇足に見えるかもですが、ORDER BY句にANDやORの条件式を設定できると気が付かない初心者の方もおられるかも知れないので、やや無理やりながらも例題とサンプルを書いてみました。
もちろん本来はこのような状況にならないようにデータベース設計するのが大事です。INTEGER項目に空文字が入るなんて腰が抜けそうな事態にはするべきではないｗ
でも実運用上、PHPプログラム側に問題があったり、予期せぬすり抜けデータが入ってきたり、そんなことは往々にしてあり得ます。それでもDB側で弾いてくれれば良いのですが、SQLiteの場合は型親和性という仕様によりINTEGERとして定義された項目に文字列を入れることが出来てしまいます。
そうなるとプログラム側で厳密にチェックするべきですが、何らかの事情ですぐには修正できないケースもあるでしょう。更新部と表示部で開発担当者が違う、とか、単に表示部より更新部のほうが数が多いから、取り急ぎ手直ししたい、等。
そんなこんなで今回の例はレアケースだとは思いますが、ORDER BYはけっこう融通が効くのだぞ、ということだけでも伝われば幸いです。
