Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Разница в результатах NOT EXISTS & NOT IN  [new]
Eugenius
Member

Откуда:
Сообщений: 176
Доброго времени суток.
Не стал это вписывать в тему от Newber
поскольку вопрос не в том, что лучше, а почему результаты разные:
запрос с условием
WHERE <ПОЛЕ> NOT IN (SELECT DISTINCT <другое поле> FROM <ДРУГАЯ ТАБЛИЦА>)
не дает результатов
а тот же запрос с условием
WHERE NOT EXISTS (SELECT DISTINCT <другое поле> FROM <ДРУГАЯ ТАБЛИЦА> WHERE <другое поле>=<ПОЛЕ>)
результат возвращает.

Не понимаю. Снимите с меня лыжи, пожалуйста (если дело в них):-)
Спасибо.
24 май 11, 20:48    [10702738]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
iljy
Member

Откуда:
Сообщений: 8711
Eugenius,

это классика жанра
declare @t table (id int)
insert @t values (1),(2),(3),(null),(5)

select 1 where 4 not in (select id from @t)
select 1 where not exists(select id from @t where id=4)

PS А нафига DISTINCT внутри EXISTS? Чтоб серверу интереснее было?
24 май 11, 20:52    [10702751]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
Eugenius
Member

Откуда:
Сообщений: 176
iljy
PS А нафига DISTINCT внутри EXISTS? Чтоб серверу интереснее было?

Да просто остался при копировании:-)
в реальном запросе нет
24 май 11, 20:53    [10702755]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
Eugenius
Member

Откуда:
Сообщений: 176
iljy
Eugenius,

это классика жанра
declare @t table (id int)
insert @t values (1),(2),(3),(null),(5)

select 1 where 4 not in (select id from @t)
select 1 where not exists(select id from @t where id=4)

То есть дело в наличии хотя бы одного значения null в результатах подзапроса?
24 май 11, 20:54    [10702761]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
Eugenius
Member

Откуда:
Сообщений: 176
iljy,

Спасибо!
24 май 11, 20:57    [10702774]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
iljy
Member

Откуда:
Сообщений: 8711
Eugenius
То есть дело в наличии хотя бы одного значения null в результатах подзапроса?

Ага. И в троичной логике скуля.
24 май 11, 21:00    [10702784]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
Eugenius
Member

Откуда:
Сообщений: 176
iljy,

не вкурил до этого, как троичная логика с предикатами работает, теперь понятно
24 май 11, 21:04    [10702800]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
iljy
Member

Откуда:
Сообщений: 8711
Eugenius
iljy,

не вкурил до этого, как троичная логика с предикатами работает, теперь понятно

А чего тут вкуривать? В БОЛ есть таблицы значений, а IN - это фактически OR.
24 май 11, 22:47    [10703110]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
Eugenius
Member

Откуда:
Сообщений: 176
iljy
Eugenius
iljy,

не вкурил до этого, как троичная логика с предикатами работает, теперь понятно

А чего тут вкуривать? В БОЛ есть таблицы значений, а IN - это фактически OR.


Разница между IN и OR как раз есть: попробуй 2 условия с OR, одно из которых возвращает UNKNOWN - если второе выполнится, то и всё выражение будет иметь значение TRUE

if (null = null)
	select 2
else if (null = null or 1 = 1)
	select 1
else
	select 0
25 май 11, 09:25    [10703912]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Eugenius
iljy
пропущено...

А чего тут вкуривать? В БОЛ есть таблицы значений, а IN - это фактически OR.


Разница между IN и OR как раз есть: попробуй 2 условия с OR, одно из которых возвращает UNKNOWN - если второе выполнится, то и всё выражение будет иметь значение TRUE

if (null = null)
	select 2
else if (null = null or 1 = 1)
	select 1
else
	select 0
Нет никакой разницы:
IF 1 IN(NULL,1) PRINT 'Ok';
Однако
IF 0 NOT IN(NULL,1) PRINT 'Ok' ELSE PRINT 'Bad';
Не претендую на истину в последней инстанции, но 10436824
Почитайте, например, Ицик Бен-Ган. Microsoft SQL Sever 2008. Основы T-SQL
Там это очень хорошо разжёвано
25 май 11, 09:56    [10704117]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
iljy
Member

Откуда:
Сообщений: 8711
Eugenius
Разница между IN и OR как раз есть

Да ну?
declare @d int = 2
select case when @d in (0,1,null) then 1 else 0 end [in],
	case when @d not in (0,1,null) then 1 else 0 end [not in],
	case when @d = 0 or @d = 1 or @d = null then 1 else 0 end [or],
	case when not(@d = 0 or @d = 1 or @d = null) then 1 else 0 end [not or]
А потом замените на @d = 0.
25 май 11, 10:30    [10704345]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
Eugenius
Member

Откуда:
Сообщений: 176
iljy
Eugenius
Разница между IN и OR как раз есть

Да ну?
declare @d int = 2
select case when @d in (0,1,null) then 1 else 0 end [in],
	case when @d not in (0,1,null) then 1 else 0 end [not in],
	case when @d = 0 or @d = 1 or @d = null then 1 else 0 end [or],
	case when not(@d = 0 or @d = 1 or @d = null) then 1 else 0 end [not or]
А потом замените на @d = 0.


Ок, признаю, зря не перепроверил собственный пример.
25 май 11, 10:57    [10704615]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
Newber
Member

Откуда: Planet Earth
Сообщений: 107
Eugenius,

ну да, все песня получается по тому что.
in просто сравнивает обычным сравнением
а при null надо юзать is
а он их не разделяет, поэтому так и получается.
25 май 11, 11:47    [10705162]     Ответить | Цитировать Сообщить модератору
 Re: Разница в результатах NOT EXISTS & NOT IN  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Однако:
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'T','U') IS NOT NULL DROP TABLE T;
CREATE TABLE T(V INT CHECK(V NOT IN(0,1,NULL)));
INSERT T(V) VALUES(0);
INSERT T(V) VALUES(1);
INSERT T(V) VALUES(NULL);
INSERT T(V) VALUES(-1);
SELECT * FROM T;
DROP TABLE T;
!!!

А всё потому, что WHERE пропускает только записи, для которых выражение вернуло TRUE, и отбрасывает FALSE и UNKNOWN,
а CHECK CONSTRAINT не пропускает записи, для которых выражение вернуло FALSE, и принимает TRUE и UNKNOWN.

По-разному работают в отношении UNKNOWN, проще говоря.
25 май 11, 12:43    [10705794]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить