Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Не работает ... where in (select a ....  [new]
FOKS
Member

Откуда:
Сообщений: 34
Добрый день.

Операция in не находит очевидных вещей, SQL Server 2008.
Ситуация следующая.
Есть две связанных таблицы по полю city_id, типа int, относительно не большие (10 000 и 300 000 записей)

Запросы:

select *
from a
where city_id = 20428

возвращает одну запись

select *
from b
where city_id = 20428

не возвращает ни одной записи

select *
from a
where city_id not in
(select city_id
from b)

ничего не возвращает. ПОЧЕМУ?

select *
from a
left outer join
b
on a.city_id=b.city_id
where b.zzzzz is null

возвращает что надо - 86 записей, включая city_id = 20428

Что делал:
+checktable - нет ошибок
+перестроил все индексы - не помогло
+with(nolock) - не помогло
+посмотрел план выполнения (они весьма разные для запросов), и пристрелил индекс, который используется в глючном запросе, запустил, он переключился на другие (по плану посмотрел), чуть дольше отработал, опять ничего не вернул
+ крутил селект по разному, но он ни разу так ничего и не выдал


Куда копать?
28 мар 11, 14:57    [10433955]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
iljy
Member

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

declare @t table (a int)
insert @t values(1),(2)
select 1 where 3 not in(select a from @t)
insert @t values(null)
select 1 where 3 not in(select a from @t)
28 мар 11, 14:59    [10433970]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
FOKS
Member

Откуда:
Сообщений: 34
СПАСИБО!

Для моего случая получается:

select *
from a
where city_id not in
(select city_id
from b
where city_id is not null)

Но почему так??? Объясните пожалуйста.
28 мар 11, 15:09    [10434061]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
FOKS
Но почему так??? Объясните пожалуйста.

Потому что хелп иногда надо читать:

IN (Transact-SQL) ( ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/4419de73-96b1-4dfe-8500-f4507915db04.htm )
Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.
28 мар 11, 15:11    [10434084]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
iap
Member

Откуда: Москва
Сообщений: 47097
FOKS,

почитайте, например, эту книжку.
Там очень подробно разбирается этот эффект (естественно, не только там).
28 мар 11, 16:02    [10434449]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
FOKS
Member

Откуда:
Сообщений: 34
Что оно работает так я понял.
Разбирать всю книгу ради одного нюанса - тоже не хороший совет.
Всё равно моей логике не поддаётся почему так. :-(
А работает так, запомни, и не думай об этом - так не интересно.
29 мар 11, 05:00    [10436524]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
FOKS
Member

Откуда:
Сообщений: 34
Моё понимание

Есть четыре банки:
- в одной камень
- в другой вода
- в третьей песок
- в четвёртой пусто (null)

Я говорю: найди ка мне уважаемый SQL банки, в которых нет песка

А мне SQL говорит - нет таких банок, везде песок

Это ведь не правильно!

Почему я его должен спрашивать - найди мне банки, где нет песка, и где не пусто?

Где в моих размышлениях ошибка?
29 мар 11, 05:06    [10436525]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> Где в моих размышлениях ошибка?

при сравнении null интерпретируется не как пустое, а как _неизвестное_ значение.
то есть, результат сравнения чего-либо с null - это не истина и не ложь.
это unknown - неизвестно. дали вам те самые четыре банки: в одной камень, в другой
вода, в третьей песок, а в четвертой - неизвестно что (null). да и попросили определить,
что ни в одной из банок пива нет. ну и что отвечать будете? есть оно там, или нет?
в одной-то из банок неизвестно что находится. и так - для любого значения, о чем
бы ни спросили. никогда вы не сможете утвердительно ответить - того, о чем
вас спрашивают, в банках нет. либо отрицательно (камень, вода и песок там есть),
либо неизвестно.

Posted via ActualForum NNTP Server 1.4

29 мар 11, 09:01    [10436712]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
iap
Member

Откуда: Москва
Сообщений: 47097
Чисто формально можно посмотреть сюда: Значения NULL

В следующей таблице показаны результаты применения оператора AND к двум логическим операндам.
ANDTRUEUNKNOWNFALSE
TRUETRUEUNKNOWNFALSE
UNKNOWNUNKNOWNUNKNOWNFALSE
FALSEFALSEFALSEFALSE

В следующей таблице показаны результаты применения оператора OR к двум логическим операндам.
ORTRUEUNKNOWNFALSE
TRUETRUETRUETRUE
UNKNOWNTRUEUNKNOWNUNKNOWN
FALSETRUEUNKNOWNFALSE

В следующей таблице показано, как оператор NOT выполняет логическое отрицание (или обращение) результата логического оператора.
Логическое выражение, к которому применяется оператор NOTРезультат
TRUEFALSE
UNKNOWNUNKNOWN
FALSETRUE

Для проверки наличия значений NULL стандарт ISO использует ключевые слова IS NULL и IS NOT NULL.
Логическое выражение, к которому применяется оператор IS NULLРезультатЛогическое выражение, к которому применяется оператор IS NOT NULLРезультат
TRUEFALSETRUETRUE
NULLTRUENULLFALSE
FALSEFALSEFALSETRUE


Теперь представим
X NOT IN (SELECT [Field] FROM [Table])
в виде
X NOT IN(0, 1, NULL)
где 0, 1 и NULL - значения поля [Field].
X NOT IN(0, 1, NULL)
эквивалентно
NOT (X = 0 OR X = 1 OR X = NULL)
Теперь достаточно воспользоваться приведёнными выше таблицами и убедиться,
что первоначальное логическое выражение равно либо FALSE, либо UNKNOWN.
А WHERE пропускает только записи, для которых логическое выражение возвращает TRUE.
29 мар 11, 09:32    [10436824]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
FOKS
Моё понимание

+ Его понимание
Есть четыре банки:
- в одной камень
- в другой вода
- в третьей песок
- в четвёртой пусто (null)

Я говорю: найди ка мне уважаемый SQL банки, в которых нет песка

А мне SQL говорит - нет таких банок, везде песок

Это ведь не правильно!

Почему я его должен спрашивать - найди мне банки, где нет песка, и где не пусто?

Где в моих размышлениях ошибка?


Откуда вообще берётся такое понимание, которое противоречит и теории, и документации?
29 мар 11, 10:05    [10436989]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
stukselbax
Member

Откуда:
Сообщений: 22
Всё приходит с опытом! Наступишь пару раз на грабли - на третий раз задумаешься) учиться никогда не поздно.
29 мар 11, 10:13    [10437037]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
J.d
Member

Откуда: Москва
Сообщений: 691
Зайцев Фёдор,

это наверно от того что NULL похоже на НОЛЬ. -> пусто, ничего. )) если вот было бы Unknown или undefined, то возможно люди понимали бы правильнее. =)
29 мар 11, 12:32    [10438267]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
bibiskula
Member

Откуда: Пöндус(Инöстрäнный öгент)
Сообщений: 52988
FOKS
Что оно работает так я понял.
Разбирать всю книгу ради одного нюанса - тоже не хороший совет.
Всё равно моей логике не поддаётся почему так. :-(
А работает так, запомни, и не думай об этом - так не интересно.

Ну для того чтобы не читать всю книгу можете посмотреть 14.3. NULL-значения и предикат IN(искать по тексту ссылки)
29 мар 11, 14:08    [10439160]     Ответить | Цитировать Сообщить модератору
 Re: Не работает ... where in (select a ....  [new]
FOKS
Member

Откуда:
Сообщений: 34
Всем большое спасибо за подробные разъяснения.
Я стал умнее. :-)
30 мар 11, 10:17    [10443513]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить