「order by句でnullを後ろにするSQL」を使用するシチュエーション
Webシステム開発をしているとよく使うSQL文の１つです。
例えばあるテーブルの中身を一覧表示するときに、表示順を管理者なりの設定で決められるようにするため、順番を決める用のカラムを用意するとします。
このとき、優先的に表示したいレコード順にそのカラムに1,2,3・・・と値を入れていくと、orderby句でそのカラムを指定して昇順・降順にすればOKです。
しかし、優先度の低いもので特に何番目でも構わないレコードがあった場合、値をいくつにすればいいのか決められません。
このように特に優先する必要のないレコードもあるような場合、そのカラムをNULLにしておいて、NULLになっているレコードは最後に表示するようなSQLを使うことで、表示順が明示されているものはその順番に、明示されていないものは最後にまとめて表示することができるようになります。
「order by句でnullを後ろにするSQL」を使用する具体例
それでは具体例を見ていきましょう。
まずは今回サンプルで使う「sample」テーブルを作成します。
create table sample(
id int primary key auto_increment,
title text not null,
disp_order int
);
insert into sample(title,disp_order) values('aaa',3);
insert into sample(title,disp_order) values('bbb',1);
insert into sample(title,disp_order) values('ccc',null);
insert into sample(title) values('ddd');
insert into sample(title,disp_order) values('eee',2);
こんな形のsampleテーブルができます。
+----+-------+------------+ | id | title | disp_order | +----+-------+------------+ | 1 | aaa | 3 | | 2 | bbb | 1 | | 3 | ccc | NULL | | 4 | ddd | NULL | | 5 | eee | 2 | +----+-------+------------+
ではこの「disp_order」カラムの順番に表示しつつ、NULLが最後に表示されるようにしてみましょう。
select * from books order by disp_order is null,disp_order asc;
以下のような実行結果になりました。
+----+-------+------------+ | id | title | disp_order | +----+-------+------------+ | 2 | bbb | 1 | | 5 | eee | 2 | | 1 | aaa | 3 | | 3 | ccc | NULL | | 4 | ddd | NULL | +----+-------+------------+
別のパターンもみてみましょう。
select * from sample order by disp_order is null desc,disp_order asc;
今度は以下のようになりました。NULLを先に出して、残りは昇順です。
+----+-------+------------+ | id | title | disp_order | +----+-------+------------+ | 3 | ccc | NULL | | 4 | ddd | NULL | | 2 | bbb | 1 | | 5 | eee | 2 | | 1 | aaa | 3 | +----+-------+------------+
今度はNULLを先に出して、残りを降順にしてみましょう。
select * from sample order by disp_order is null desc,disp_order desc;
以下のようになります。
+----+-------+------------+ | id | title | disp_order | +----+-------+------------+ | 3 | ccc | NULL | | 4 | ddd | NULL | | 1 | aaa | 3 | | 5 | eee | 2 | | 2 | bbb | 1 | +----+-------+------------+
最後はNULLを後に出して、残りを降順にしてみましょう。
select * from sample order by disp_order is null,disp_order desc;
+----+-------+------------+ | id | title | disp_order | +----+-------+------------+ | 1 | aaa | 3 | | 5 | eee | 2 | | 2 | bbb | 1 | | 3 | ccc | NULL | | 4 | ddd | NULL | +----+-------+------------+
まとめ「order by句でnullを後ろにするSQL」の構文
では最後に構文をまとめておきます。
select * from 【テーブル名】 order by 【ソートキーのカラム】 is null 【asc/desc】,【ソートキーのカラム】 【asc/desc】;
※一つ目のasc/descをascにするとNULLが後ろに、descにするとNULLが前にくる。
※二つ目のasc/descをascにするとNULL以外のソートが昇順に、descにすると降順になる。
