Oracle 空文字比較の厄介

はじめに

空文字と null を区別しない Oracle と、PostgreSQL のように''空文字と null を区別するデータベースの双方に対応するアプリケーションにしたい場合を考えます。

val に値が無いレコードは、Oracle でも PostgreSQL でも、以下のSQLで取得することができます。

select * from example where (val is null or val = '');

逆に val に値が有るレコードを取得する場合、not で否定すれば良いように思えます。

select * from example where not (val is null or val = '');

しかしこのSQLは、Oracle では機能しません。

Oracle における空文字の扱い

多くのまともなデータベースと異なり、Oracle は空文字を null として扱います。

文字列型の列に空文字を Insert した場合、Oracle は 空文字を null 値として Insert します。

example テーブルにレコードを Insert した場合は以下のようになります。

INSERT INTO example(id, val) VALUES (1, 'abc');
INSERT INTO example(id, val) VALUES (3, '');
INSERT INTO example(id, val) VALUES (2, null);

id	val
1	abc
2	null
3	null

null かそうでないかは、is null, is not null で条件を指定します。

select * from example where val is null;

id	val
2	null
3	null

select * from example where val is not null;

id	value
1	abc

ここで、is null, is not null ではなく、空文字と比較した場合は以下のようになります。

select * from example where val = '';
select * from example where val <> '';
select * from example where val != '';

id	val

いずれの場合も結果はゼロ件になります。

つまり、以下のように評価されていることになります。

select * from example where val = null;
select * from example where val <> null;
select * from example where val != null;

Oracle では、条件を null で使用すると、結果は null 値に依存して UNKNOWN になります。

条件	aの値	評価
a IS NULL	10	FALSE
a IS NOT NULL	10	TRUE
a IS NULL	NULL	TRUE
a IS NOT NULL	NULL	FALSE
a = NULL	10	UNKNOWN
a != NULL	10	UNKNOWN
a = NULL	NULL	UNKNOWN
a != NULL	NULL	UNKNOWN
a = 10	NULL	UNKNOWN
a != 10	NULL	UNKNOWN

UNKNOWN に評価された結果は、false とほぼ同様に動作します。が、同じという訳ではありません。

UNKNOWN の扱い

null との比較は UNKNOWN として評価され、false とほぼ同様に動作するため、以下の SQL で結果が取得できるように思えます。

select * from example where not (val = '');
select * from example where not (val <> '');
select * from example where not (val != '');

id	val

これらの SQL はいずれの場合も結果が得られません。

ここに UNKNOWN と false の違いがあります。

UNKNOWN に評価される条件は、UNKNOWN 条件の評価でさらに操作を行う場合は UNKNOWN に評価されるため false とは異なります。

NOT false は true に評価されますが、NOT UNKNOWNは UNKNOWN に評価されます。これにより、上記 SQL は結果を返しません。

論理条件による真理値表は以下のように定義されています。

• NOT真理値表

--	TRUE	FALSE	UNKNOWN
NOT	false	true	UNKNOWN

UNKNOWN は常に UNKNOWN です。

• AND真理値表

AND	TRUE	FALSE	UNKNOWN
TRUE	true	false	UNKNOWN
FALSE	false	false	false
UNKNOWN	UNKNOWN	false	UNKNOWN

false AND UNKNOWN が false になる点がポイントです。

• OR真理値表

OR	TRUE	FALSE	UNKNOWN
TRUE	true	true	true
FALSE	true	false	UNKNOWN
UNKNOWN	true	UNKNOWN	UNKNOWN

AND 条件とは逆で、true AND UNKNOWN が true になる点がポイントです。

Oracle と PostgreSQL 双方で動くSQL

val に値が有るレコードを取得するSQLを考えます。

SQLは Oracle と PostgreSQL の双方で同じ結果となるものを目指します。

前述の通り、以下の SQL は Oracle で機能しません。

select * from example where not (val is null or val = '');

前述の真理値表を元に、条件を組み立てると、最終的に true となるレコード(右端)が無いため結果レコードはゼロ件になります。

id	val	val is null	val = ''	or	not
1	abc	false	UNKNOWN	UNKNOWN	UNKNOWN
2	null	true	UNKNOWN	true	false

not 条件が無ければ、以下のように id:2 のレコードを得ることができます。

select * from example where (val is null or val = '');

id	val	val is null	val = ''	or
1	abc	false	UNKNOWN	UNKNOWN
2	null	true	UNKNOWN	true

このようなケースでは、LENGTH() 関数を使うと、Oracle でも PostgreSQL でも正しい結果を得ることができます。

select * from example where     (val is null or LENGTH(val) = 0);
select * from example where not (val is null or LENGTH(val) = 0);

LENGTH(val) は val の値が null の場合、結果は null となります。

それぞれを真理値表を元に、どのように論理が遷移するかを見ていきましょう。

最初は否定のないもので、値の無いものを取得します。

select * from example where     (val is null or LENGTH(val) = 0);

Oracle の場合 id:2 のレコードが抽出されます。

id	val	val is null	LENGTH(val) = 0	or
1	abc	false	false	false
2	null	true	UNKNOWN	true

PostgreSQL の場合 id:2 と id:3 のレコードが抽出されます。

id	val	val is null	LENGTH(val) = 0	or
1	abc	false	false	false
2	null	true	null	true
3	``	false	true	true

なお、PostgreSQL では UNKNOWN ではなく null として扱います。

次にnot 条件が付与されたもので、値のあるものを取得します。

select * from example where not (val is null or LENGTH(val) = 0);

Oracle の場合 id:1 のレコードが抽出されます。

id	val	val is null	LENGTH(val) = 0	or	not
1	abc	false	false	false	true
2	null	true	UNKNOWN	true	false

PostgreSQL の場合 id:1 のレコードが抽出されます。

id	val	val is null	LENGTH(val) = 0	or	not
1	abc	false	false	false	true
2	null	true	null	true	false
3	``	false	true	true	false