なんだこりゃ。SQLの「IS DISTINCT FROM」演算子

postgres rdbms

発端

ふと、「IS NOT DISTINCT FROM」あるいは「IS DISTINCT FROM」という文字列が含まれるSQL文を目にしました。
SQLの基本的な構文として、

SELECT DISTINCT col1, col2 FROM t1;

とか書くので、そのDISTINCT と FROM が、、、、とか考えていると混乱します。とりあえず「単にこういう長い名前の記号」と思っておくのがよさそう(笑)。

これはなに

Oracleから入り、その後MySQLをメインとするようになった私の通った道には、こんな構文はなかったわけです。それにしても、SQLiteでさえ（←ひどい言い草）対応しているのにMySQLにないってのは、ちょっと悔しい(笑)。

PostgreSQL 16 で動作を確認

こんなデータを作った。

\pset null (null)
create table t1 (id integer, s varchar(10));

SELECT * FROM t1;
 id |   s    
----+--------
  1 | AA
  2 | CBB
  3 | AA
  4 | CC
  5 | 
  6 | CC
  7 | 
  8 | (null)
  9 | (null)
(9 rows)

単純に結合するとこんな感じ（自分自身との結合を除外）。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.id<>tb.id;
 id |  s  | id |  s  
----+-----+----+-----
  2 | CBB |  1 | AA
  3 | AA  |  1 | AA
  4 | CC  |  1 | AA
 :
  5 |     |  9 | 
  6 | CC  |  9 | 
  7 |     |  9 | 
  8 |     |  9 | 
(72 rows)

値が同じのだけを抽出したい場合は、（今までの私の発想だと）イコールを使う。ここに null のものは現れない。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s = tb.s AND ta.id<>tb.id;
 id | s  | id | s  
----+----+----+----
  1 | AA |  3 | AA
  4 | CC |  6 | CC
  5 |    |  7 | 
  3 | AA |  1 | AA
  6 | CC |  4 | CC
  7 |    |  5 | 
(6 rows)

IS NOT DISTINCT FROM を使うと、nullも「nullという値だとみなして」一致比較をしてくれる。「nullという値」というとっても気持ち悪いパワーワードですが「みなして」ということで我慢することにします:-)

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s IS NOT DISTINCT FROM tb.s AND ta.id<>tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  3 | AA     |  1 | AA
  1 | AA     |  3 | AA
  6 | CC     |  4 | CC
  7 |        |  5 | 
  4 | CC     |  6 | CC
  5 |        |  7 | 
  9 | (null) |  8 | (null)
  8 | (null) |  9 | (null)
(8 rows)

余談

本題ではないのだけど、今回試したクエリで、行きと帰りの一致データが重複して出ているのが気になりますよね。
今回は「IDが一致しないもの」ということで自分自身との結合を除外しましたが、この条件を「IDが自分よりも小さいもの」とだけ比較するようにすると片道切符になります。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s IS NOT DISTINCT FROM tb.s AND ta.id<tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  1 | AA     |  3 | AA
  4 | CC     |  6 | CC
  5 |        |  7 | 
  8 | (null) |  9 | (null)
(4 rows)

最初からこちらでやっていたほうが、結果がシンプルになりましたね。


応用例

IS NOT DISTINCT FROM ではなく IS DISTINCT FROM を使うと、不一致のものにマッチさせることができます。
たとえば、値が'AA'のものと、これに一致しないもののIDの対応表を作りたい時に、こんなふうに。
対象としてnullも含まれるところが、新しいところです（べつに新しくないのですが、= とか <> しか知らなかった私には新しい）。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s='AA' AND ta.s IS DISTINCT FROM tb.s AND ta.id<tb.id;
 id | s  | id |   s    
----+----+----+--------
  1 | AA |  2 | CBB
  1 | AA |  4 | CC
  1 | AA |  5 | 
  1 | AA |  6 | CC
  1 | AA |  7 | 
  1 | AA |  8 | (null)
  1 | AA |  9 | (null)
  3 | AA |  4 | CC
  3 | AA |  5 | 
  3 | AA |  6 | CC
  3 | AA |  7 | 
  3 | AA |  8 | (null)
  3 | AA |  9 | (null)
(13 rows)

まとめ

今回の構文が長いので IS DISTINCT FROM を [コレ] と書くとすると、
A [コレ] B は、AがBと違っているときに成立(true)、
A NOT[コレ] B は、AがBと違ってないとき(笑)、、、つまり同じ時ですね、、に成立。（正確にはNOTの位置は IS NOT の場所になります）

既にSQL構文の中で使われている DISTINCT とか FROM という単語をこういう形でまったく違う用途で使うのって、仕様決めた人はセンスないよなと私は思ってしまうのですが、たぶん私など理解不能なレベルで考えぬいた末に、きっとセンスの塊の成果として決まったものだと思います（思いたい）。

軽く調べてみたのですが、 SQL-92には含まれていなくて、SQL-1999で登場した記法のようです。
近年のMySQL開発はどんどん、なるべく標準に準拠するようにとうことを心がけているように見えるので、そのうちこの構文が導入されたりするのかな。新機能好きなので楽しみに待ちたいと思います。導入されたら「知ってる！知ってる！導入前から注目していたんですよ！」と自慢できるように、このエントリを書いておきました（そんなわけじゃないｗ）。




参照

Twitter(X)で色々おしえてもらいました。ありがとうございました！

Snowflakeのマニュアル、わかりやすい。
https://docs.snowflake.com/ja/sql-reference/functions/is-distinct-from


追記

要するにこういうことなのか。
何か新しい事ができるようになったというよりは、一種のシンタックスシュガーと捉えても良いのかな。
（ここで「絶対に既存のデータと被らない置換文字列」を決める部分で「絶対」を保証できないので、IS DISTINCT FROM の存在意義が出てくるわけですが）

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE COALESCE(ta.s,'*+;:')=COALESCE(tb.s,'*+;:') AND ta.id<tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  1 | AA     |  3 | AA
  4 | CC     |  6 | CC
  5 |        |  7 | 
  8 | (null) |  9 | (null)
(4 rows)
db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s=tb.s AND ta.id<tb.id;
 id | s  | id | s  
----+----+----+----
  1 | AA |  3 | AA
  4 | CC |  6 | CC
  5 |    |  7 | 
(3 rows)
db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s IS NOT DISTINCT FROM tb.s AND ta.id<tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  1 | AA     |  3 | AA
  4 | CC     |  6 | CC
  5 |        |  7 | 
  8 | (null) |  9 | (null)
(4 rows)