DSQL PSQL

DISTINCT используется в конструкции SELECT и как самостоятельный оператор

переменная IS [NOT] DISTINCT FROM переменная2

сравнивает 2 значения невзирая на NULL

если на поле существует индекс то он может быть использован при NOT DISTINCT

Блок

EXECUTE block
RETURNS (s VARCHAR(100))
AS
BEGIN
  IF (NULL = NULL) THEN
  BEGIN
    s = 'null = null';
    suspend;
  END
 
  IF (NULL <> NULL) THEN
  BEGIN
    s = 'null <> null';
    suspend;
  END
 
  IF (NULL = 12) THEN
  BEGIN
    s = 'null = 12';
    suspend;
  END
 
  IF (NULL <> 12) THEN
  BEGIN
    s = 'null <> 12';
    suspend;
  END
 
  IF (12 <> 12) THEN
  BEGIN
    s = '12 <> 12';
    suspend;
  END
 
  IF (12 = 12) THEN
  BEGIN
    s = '12 = 12';
    suspend;
  END
 
  IF (NULL IS DISTINCT FROM NULL) THEN
  BEGIN
    s = 'null is distinct from null';
    suspend;
  END
 
  IF (NULL IS NOT DISTINCT FROM NULL) THEN
  BEGIN
    s = 'null is not distinct from null';
    suspend;
  END
 
  IF (NULL IS DISTINCT FROM 12) THEN
  BEGIN
    s = 'null is distinct from 12';
    suspend;
  END
 
  IF (12 IS DISTINCT FROM NULL) THEN
  BEGIN
    s = '12 is distinct from null';
    suspend;
  END
 
  IF (12 IS DISTINCT FROM 12) THEN
  BEGIN
    s = '12 is distinct from 12';
    suspend;
  END
 
  IF (12 IS NOT DISTINCT FROM 12) THEN
  BEGIN
    s = '12 is not distinct from 12';
    suspend;
  END
 
  IF (12 IS DISTINCT FROM 13) THEN
  BEGIN
    s = '12 is distinct from 13';
    suspend;
  END
 
END;

результат

12 = 12
null is not distinct from null
null is distinct from 12
12 is distinct from null
12 is not distinct from 12
12 is distinct from 13

SELECT * FROM T1 JOIN T2 ON T1.NAME IS NOT DISTINCT FROM T2.NAME;

SELECT * FROM T WHERE T.MARK IS DISTINCT FROM 'test'

COALESCE NULLIF

README.distinct.txt