SQL で NULL は DISTINCT だが UNIQUE では無い: uyota 匠の一手

とても変な言葉。「DISTINCT」は「特別な」、「他とは違う」と言う意味を持っている。「UNIQUE」も「唯一無二」、「替えの効かない」等と言う意味。

NULL は DISTINCT だと NULL 同士が同じ値だと評価されるが、UNIQUE で NULL 同士を比べると全てが異なると処理される。

SQLite での例。

sqlite> CREATE TABLE table1 ( number1 integer UNIQUE, number2 integer );
sqlite> INSERT INTO table1 VALUES ( NULL, 1 );
sqlite> INSERT INTO table1 VALUES ( NULL, 2 );
sqlite> INSERT INTO table1 VALUES ( 11, 11 );
sqlite> INSERT INTO table1 VALUES ( 11, 12 );
Error: UNIQUE constraint failed: table1.number1
sqlite> SELECT DISTINCT number1 FROM table1;

11
sqlite> SELECT number1 FROM table1;


11
sqlite>

PostgreSQL での例。

postgres=# CREATE TABLE table1 ( number1 integer UNIQUE, number2 integer );
CREATE TABLE
postgres=# INSERT INTO table1 VALUES ( NULL, 1 );
INSERT 0 1
postgres=# INSERT INTO table1 VALUES ( NULL, 2 );
INSERT 0 1
postgres=# INSERT INTO table1 VALUES ( 11, 11 );
INSERT 0 1
postgres=# INSERT INTO table1 VALUES ( 11, 12 );
ERROR:  duplicate key value violates unique constraint "table1_number1_key"
DETAIL:  Key (number1)=(11) already exists.
postgres=# SELECT DISTINCT number1 FROM table1;
 number1
---------

      11
(2 rows)

postgres=# SELECT number1 FROM table1;
 number1
---------


      11
(3 rows)

postgres=#

さて、違いが分かっただろうか。UNIQUE コラムを作成すると、同じ値は INSERT 出来ない。ここでは、全ての NULL は異なった値として扱われる。しかし、DISTINCT で重複を取り除くと、NULL は一つしか残されない。

PostgreSQL はこれが SQL の仕様だと述べている。SQLite は NULL は全て同じとして扱ってみたが、他の実装に合わせて UNIQUE では別の値として扱うことにした とある。

NULL の同一性が不明確で分かりづらい。