NULLのソート順

• MySQL

(8.0.29)
https://www.yoheim.net/blog.php?q=20190103

drop table tab1;
create table tab1(col1 int primary key,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(2,2);
insert into tab1 values(3,NULL);

select * from tab1 order by col2 asc;
select * from tab1 order by col2 desc;

NULLS FIRSTがデフォルト

select * from tab1 order by col2 nulls first;
select * from tab1 order by col2 nulls last;

※nulls firstやnulls lastは使用できない。

NULLS LASTにしたい場合
select * from tab1 order by col2 is null, col2 ;

• Oracle

(19c)

drop table tab1 purge;
create table tab1(col1 int primary key,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(2,2);
insert into tab1 values(3,NULL);
commit;

select * from tab1 order by col2 asc;
select * from tab1 order by col2 desc;

NULLS LASTがデフォルト

select * from tab1 order by col2 nulls first;
select * from tab1 order by col2 nulls last;

• PostgreSQL

(14)

drop table tab1;
create table tab1(col1 int primary key,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(2,2);
insert into tab1 values(3,NULL);

select * from tab1 order by col2 asc;
select * from tab1 order by col2 desc;

NULLS LASTがデフォルト

select * from tab1 order by col2 nulls first;
select * from tab1 order by col2 nulls last;

• SQL Server

(2019)
https://www.curict.com/item/fb/fb39f3e.html

drop table tab1;
create table tab1(col1 int primary key,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(2,2);
insert into tab1 values(3,NULL);

select * from tab1 order by col2 asc;
select * from tab1 order by col2 desc;

NULLS FIRSTがデフォルト

select * from tab1 order by col2 nulls first;
select * from tab1 order by col2 nulls last;

※nulls firstやnulls lastは使用できない。

NULLS LASTにしたい場合

select * from tab1 order by iif(col2 is null,1,0), col2 ;