SQLite3 ソートしたときのNULLの位置を調整する

概要

NULLを含むデータをソートした場合、 デフォルトだとNULLは最小値より小さい値としてソートされる。
nulls first、もしくは nulls last を使用することで、 NULLの位置をコントロールすることができる。

構文

NULL を最初に配置する。

order by column_name nulls first
                

NULL を最後に配置する。

order by column_name nulls last
                

実行例

環境

• Windows 10 64bit
• SQLite Command-Line Shell ver 3.50.4

以下のテーブルからデータをソートして取得する。

id	name
0	hoge1
1	hoge2
2

昇順ソートでNULLの位置を調整する

nameカラムに対し、

1. 何も指定しない場合
2. ascだけを指定した場合
3. nulls firstを指定した場合、 ascだけを指定した場合と同じ結果になる
4. ascとnulls first は一緒に指定することもできる（結果は同じ）
5. nulls lastを指定した場合、 値が入っているレコードが先に来て、nullが最後に来る

sqlite> .nullvalue [null]
sqlite> .mode box
sqlite> -- 1.
sqlite> select * from user;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 0  │ hoge1  │
│ 1  │ hoge2  │
│ 2  │ [null] │
└────┴────────┘
sqlite> -- 2.
sqlite> select * from user order by name asc;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 0  │ hoge1  │
│ 1  │ hoge2  │
└────┴────────┘
sqlite> -- 3.
sqlite> select * from user order by name nulls first;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 0  │ hoge1  │
│ 1  │ hoge2  │
└────┴────────┘
sqlite> -- 4.
sqlite> select * from user order by name asc nulls first;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 0  │ hoge1  │
│ 1  │ hoge2  │
└────┴────────┘
sqlite> -- 5.
sqlite> select * from user order by name asc nulls last;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 0  │ hoge1  │
│ 1  │ hoge2  │
│ 2  │ [null] │
└────┴────────┘
sqlite> select * from user order by name nulls last;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 0  │ hoge1  │
│ 1  │ hoge2  │
│ 2  │ [null] │
└────┴────────┘
                    

降順ソートでNULLの位置を調整する

nameカラムに対し、

1. descだけを指定した場合
2. desc nulls firstを指定した場合、 値が入っているレコードが後に来て、nullが最初に来る
3. desc nulls lastを指定した場合、 descを単独で指定した場合と同じ結果になる

sqlite> -- 1.
sqlite> select * from user order by name desc;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 1  │ hoge2  │
│ 0  │ hoge1  │
│ 2  │ [null] │
└────┴────────┘
sqlite> -- 2.
sqlite> select * from user order by name desc nulls first;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 1  │ hoge2  │
│ 0  │ hoge1  │
└────┴────────┘
sqlite> -- 3.
sqlite> select * from user order by name desc nulls last;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 1  │ hoge2  │
│ 0  │ hoge1  │
│ 2  │ [null] │
└────┴────────┘
                    

参考URL

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite ORDER BY に関するドキュメント
  https://www.sqlite.org/syntax/ordering-term.html

SQLite 3 二つの値を比較し同じ場合NULLを返す(nullif())

概要

nullif()は引数に与えられた2つの値を比較し、 同じだった場合null、違った場合第一引数を返す。
iif()の亜種のような関数。

構文

nullif(value1, value2)
                

value1

二つの引数の値が異なる場合、この引数が返される。

value2

value1 と比較する値

実行例１

環境

• Windows 10
• SQLite ver 3.45.1

sqlite> .nullvalue [null]
sqlite> select nullif(1, 1);
[null]
sqlite> select nullif(1, 0);
1
sqlite> select nullif('sample1', 'sample1');
[null]
sqlite> select nullif('sample1', 'sample2');
sample1
sqlite> select nullif('1', 1);
1
sqlite> select nullif(true, true);
[null]
sqlite> select nullif(true, false);
1
                

参考URL

• 公式のnullif()に関するドキュメント
  https://www.sqlite.org/lang_corefunc.html#nullif

• SQLite 3 NULLの場合、別の値を返す(ifnull())
  https://sfnovicenotes.blogspot.com/2024/03/sqlite-3-nullifnull.html

SQLite 3 NULLの場合、別の値を返す(ifnull())

概要

値がNULLかどうかを判定して、NULLだった場合別の値を返すようにするには ifnull()関数を使う。 ifnull()はOracleのNVL()に相当する機能。

構文

ifnull(value1, value2)
                

value1

NULLかどうかを判定する対象。この値がNULLでなければ関数はこの値をそのまま返す。

value2

value1がNULLだった場合、この関数はこの値を返す。

実行例１

環境

• Windows 10
• SQLite ver 3.45.1

sqlite> .mode box
sqlite> -- 1.
sqlite> select ifnull(null, 'sample1') as result;
┌─────────┐
│ result  │
├─────────┤
│ sample1 │
└─────────┘

sqlite> -- 2.
sqlite> select ifnull(0, 'sample2') as result;
┌────────┐
│ result │
├────────┤
│ 0      │
└────────┘
sqlite> select ifnull('not null', 'sample3') as result;
┌──────────┐
│  result  │
├──────────┤
│ not null │
└──────────┘

sqlite> -- 3.
sqlite> .nullvalue [null]
sqlite> select ifnull(null, null) as result;
┌────────┐
│ result │
├────────┤
│ [null] │
└────────┘

                

1. 第一引数がnullだった場合、第二引数の値を返す。
2. 第一引数がnullでなければ、第一引数をそのまま返す。
3. 第一引数・第二引数ともにnullだった場合、nullを返す。

実行例２

users

id	last_login	created_at
0001	2024-02-01	2024-01-31
0002		2024-02-01
0003		2024-02-03

そのまま出力。

select
    id
    , last_login
    , created_at
from
    users
;
                    

結果

id	last_login	created_at
0001	2024-02-01	2024-01-31
0002		2024-02-01
0003		2024-02-03

last_login がNULLだった場合、「not logged in」を表示する。

select
    id
    , ifnull(last_login, 'not logged in') as last_login
from
    users
;
                    

結果

id	last_login
0001	2024-02-01
0002	not logged in
0003	not logged in

last_login がNULLだった場合、created_at を換わりに取得する。

select
    id
    , ifnull(last_login, created_at) as last_login
from
    users
;
                    

結果

id	last_login
0001	2024-02-01
0002	2024-02-01
0003	2024-02-03

参考URL

• 公式のifnull()に関するドキュメント
  https://www.sqlite.org/lang_corefunc.html#ifnull

SQLite3 nullを登録できないようにカラムを設定する(NOT NULL制約)

概要

sqlite3で カラムにnullを登録できないように制限するSQL。
nullを登録出来ないようにする = 値の登録が必須、となる。 sqlite では空白文字とnullが区別されることに注意すること。

nullを登録できないようにするには、テーブル作成時にカラムに対して「NOT NULL」制約を指定すればいい。

既存のテーブルに対して「NOT NULL」制約を追加する方法もあるが、かなり面倒。

環境

• Windows 10 64bit
• SQLite3 (3.38.0) Command-Line Shell

テーブル作成時に「NOT NULL」制約を設定する

サンプルのテーブルとして、「id」「name」カラムを入力必須とし、 「mail」カラムは入力必須ではないものとする。

「test」テーブル

カラム名	型	制約
id	int	primary key not null
name	text	not null
mail	text

このテーブルを作成するためには以下のSQLを実行する。
カラム名の後にNOT NULLと記述することで、そのカラムに「NOT NULL」制約を設定出来る。

create table test (
    id     int  primary key not null
    , name text not null
    , mail text
);
                

これで「test」テーブルにデータを登録する際、 「id」カラムと「name」カラムは入力必須となる。

以下は上記のテーブルにデータを登録する例。

1. 「mail」カラムは空でも登録出来る
2. 「id」や「name」を空で挿入とすると「NOT NULL」制約でエラーになる
3. 挿入だけでなく更新時も「NOT NULL」制約でエラーになる

sqlite> -- # 1.
sqlite> insert into test (id, name) values (1, 'sample_name_1');

sqlite> -- # 2.
sqlite> insert into test (id, mail) values (2, 'sample@example.com');
Runtime error: NOT NULL constraint failed: test.name (19)

sqlite> -- # 3.
sqlite> update test set (id, mail) values (null, 'sample@example.com');
Parse error: near "values": syntax error
    update test set (id, mail) values (null, 'sample@example.com');
                 error here ---^
                

既存のテーブルのカラムに「NOT NULL」制約を設定/解除する

既存のテーブルのカラムに「NOT NULL」制約を追加する少し手間がかかる。
と言うのも、SQLite3では ALTER TABLE で「NOT NULL」制約を設定することが出来ない。

「NOT NULL」制約を設定する具体的な方法はカラムの型を変更する方法と同じ。
解除するには設定する方法と逆の手順にすればいい。

以下は比較的簡単なテーブルでの変更例。

• 「NOT NULL」制約を設定した状態のテーブルを別名で作成する。
• 元のテーブルから新規作成したテーブルへデータをコピーする。
• 元のテーブルを削除する。
• 新規作成したテーブルの名前を変更する。

sqlite< .schema test
CREATE TABLE IF NOT EXISTS "test" (
    id     int  primary key not null
    , name text not null
    , mail text
);
sqlite< create table new_test (
   ...<     id     int  primary key not null
   ...<     , name text not null
   ...<     , mail text not null
   ...< );
sqlite< insert into
   ...<     new_test
   ...< select
   ...<     *
   ...< from
   ...<     test
   ...< ;
sqlite< drop table test;
sqlite< alter table new_test rename to test;
                

元のデータにnullが含まれているとinsert ... into ... の時点で「NOT NULL」制約でエラーになるので補完しておくこと。

sqlite< .nullvalue [null]
sqlite< .mode box
sqlite< select * from test;
┌────┬──────┬────────┐
│ id │ name │  mail  │
├────┼──────┼────────┤
│ 1  │ name │ [null] │
└────┴──────┴────────┘
sqlite< insert into
   ...<     new_test
   ...< select
   ...<     *
   ...< from
   ...<     test
   ...< ;
Runtime error: NOT NULL constraint failed: new_test.mail (19)
                

上記は簡単な実行例だが、外部キー制約や参照しているビューなどがあるためさらに複雑な手順になる。 その手順については以下を参照のこと。

• カラムの型を変更する

その他 実行例で使用しているコマンドはこちら。

• Selectしたデータを他のテーブルに挿入
• コマンドラインツールでnullの表示を見やすくする
• コマンドラインツールでselectの結果を見やすくする

参考URL

• NOT NULL constraints(英語)
  公式のNOT NULL制約に関するドキュメント
  https://www.sqlite.org/lang_createtable.html#not_null_constraints
• column-constraint(英語)
  公式のカラムに設定する各種制約に関するドキュメント
  https://www.sqlite.org/syntax/column-constraint.html
• Making Other Kinds Of Table Schema Changes
  公式のテーブルスキーマ変更に関するドキュメント
  https://sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes

SQLite 3 コマンドラインツールでnullの表示を見やすくする

概要

SQLite3 のコマンドラインツールは、 デフォルトだとselect結果にnullがある場合 空白と見分けが付かない。 これを解決するために「.nullvalue」コマンドでnullに適当な文字列を割り当てて表示することができる。

ちなみに、SQLite では長さ0の文字列とnullは区別される。

実行例

実行環境

• Windows 10 64bit
• SQLite3 (3.33.0) Command-Line Shell

サンプルテーブル(product)

id primary key autoincrement	name not null	quantity default 0	remark
1	tomato	100
2	potato	120
3	pumpkin	50	(Comment...)

デフォルトの表示例

sqlite> -- # 1.
sqlite> .mode box
sqlite> -- # 2. 
sqlite> select * from product;
┌────┬─────────┬──────────┬──────────────┐
│ id │  name   │ quantity │    remark    │
├────┼─────────┼──────────┼──────────────┤
│ 1  │ tomato  │ 100      │              │
│ 2  │ potato  │ 120      │              │
│ 3  │ pumpkin │ 50       │ (Comment...) │
└────┴─────────┴──────────┴──────────────┘
                

1. 見やすくするために 「box」 モードに変更
2. selectすると値がnullの箇所には何も表示されていない

nullの表示を「(NULL)」に変更してみる

コンソール上での表示を変更しているだけで、 テーブルのデータを更新しているわけではないのでご安心を。

sqlite> -- # 1.
sqlite> .mode box
sqlite> -- # 2.
sqlite> .nullvalue (NULL)
sqlite> -- # 3.
sqlite> select * from product;
┌────┬─────────┬──────────┬──────────────┐
│ id │  name   │ quantity │    remark    │
├────┼─────────┼──────────┼──────────────┤
│ 1  │ tomato  │ 100      │ (NULL)       │
│ 2  │ potato  │ 120      │ (NULL)       │
│ 3  │ pumpkin │ 50       │ (Comment...) │
└────┴─────────┴──────────┴──────────────┘
                

1. 見やすくするために 「box」 モードに変更
2. nullの表示を「(NULL)」に変更
3. selectすると値がnullの箇所に「(NULL)」と表示されている

参考URL

• Command Line Shell For SQLite
  公式のコマンドラインツールに関するドキュメント
  https://www.sqlite.org/cli.html