DISTINCT

Версии сервера

Доступно в

DSQL PSQL

Формат

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

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

FIXME

Описание

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

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

FIXME

Пример

Блок

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

✏️ Править эту статью на GitHub