ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / where not in (...) и NULL. Почему так?
25 сообщений из 34, страница 1 из 2
  1  все
where not in (...) и NULL. Почему так?
    #38825001
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
Такой код:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create table #t1 (ni int null)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)

create table #z1 (ni int)
insert into #z1 values (1)
insert into #z1 values (3)
insert into #z1 values (5)

select * from #z1 where ni not in (select ni from #t1)
insert into #t1 values (NULL)
select * from #z1 where ni not in (select ni from #t1)

drop table #z1
drop table #t1


select в первом случае возвращает одну запись (5), а после добавления NULL - ни одной.
Почему так? Это где-то описано в стандарте SQL?

select @@version
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
...
Рейтинг: 0 / 0
04.12.2014, 15:14
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825007
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Dima T,

not in (null) что должен вернуть?
...
Рейтинг: 0 / 0
04.12.2014, 15:19
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825011
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
Dima T, открываете официальную справку по IN и видите выделенное предупреждение:

Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN.
...
Рейтинг: 0 / 0
04.12.2014, 15:20
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825012
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
Владислав КолосовDima T,

not in (null) что должен вернуть?
Всё. Я так думал. Вот и спрашиваю что не так я понимаю.
...
Рейтинг: 0 / 0
04.12.2014, 15:21
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825020
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
Shakill, спасибо.
...
Рейтинг: 0 / 0
04.12.2014, 15:28
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825076
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Почему всё? Он вернет "неизвестно", а такое значение не попадает в результат. В результат попадают только достоверные значения.
...
Рейтинг: 0 / 0
04.12.2014, 15:58
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825101
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Владислав КолосовПочему всё? Он вернет "неизвестно", а такое значение не попадает в результат. В результат попадают только достоверные значения.Этот пример неинтересен.
А вот если кроме одного NULL в списке тысяча значений, которым удовлетворяет NOT IN(),
то всё равно результат будет UNKNOWN. Ибо логически неизвестно, может NULL, представляющий
понятие "UNKNOWN", "включает в себя" запрещённое значение, а может нет.
...
Рейтинг: 0 / 0
04.12.2014, 16:11
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825102
JeStone
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JeStone
Участник
Dima T,
Все дело в особенностях троичной логики.
Почему так происходит даже на хабре писали. Вот ссылочка
Раздел "Операторы IN и NOT IN" с разложением на бинарное дерево
...
Рейтинг: 0 / 0
04.12.2014, 16:11
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825107
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
iapВладислав КолосовПочему всё? Он вернет "неизвестно", а такое значение не попадает в результат. В результат попадают только достоверные значения.Этот пример неинтересен.
А вот если кроме одного NULL в списке тысяча значений, которым удовлетворяет NOT IN(),
то всё равно результат будет UNKNOWN. Ибо логически неизвестно, может NULL, представляющий
понятие "UNKNOWN", "включает в себя" запрещённое значение, а может нет.

ну это примерно как конкатенировать обычную строку и NULL
...
Рейтинг: 0 / 0
04.12.2014, 16:14
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825111
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
JeStoneDima T,
Все дело в особенностях троичной логики.
Почему так происходит даже на хабре писали. Вот ссылочка
Раздел "Операторы IN и NOT IN" с разложением на бинарное деревоМожно и Ицика Бен-Гана почитать.
Он то же самое очень доходчиво рассказал словами
...
Рейтинг: 0 / 0
04.12.2014, 16:16
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825135
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
iap, да-с проблема бочки мёда и ложки дёгтя :)
...
Рейтинг: 0 / 0
04.12.2014, 16:29
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825314
Змей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Змей
Участник
все регулируется сетами
Код: sql
1.
set ansi_nulls off/on
...
Рейтинг: 0 / 0
04.12.2014, 18:15
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825315
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
Змейвсе регулируется сетами
Код: sql
1.
set ansi_nulls off/on


И вы можете это показать для приведенного в начале скрипта ?
...
Рейтинг: 0 / 0
04.12.2014, 18:16
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825325
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
На всякий случай.
авторВ будущей версии параметр SQL Server ANSI_NULLS всегда будет иметь значение ON, а приложения, явно присваивающие ему значение OFF, будут вызывать ошибку . Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.
SET ANSI_NULLS
Не знаю, когда поменяют, но лучше вообще избегать этой конструкции. Она и на планы может влиять и вообще, вносить неразбериху.
...
Рейтинг: 0 / 0
04.12.2014, 18:21
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825411
Змей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Змей
Участник
Glory,

а чего показывать то?

Код: sql
1.
2.
3.
4.
5.
---
set ansi_nulls on
select * from #z1 where ni not in (select ni from #t1)
set ansi_nulls off
select * from #z1 where ni not in (select ni from #t1)
...
Рейтинг: 0 / 0
04.12.2014, 19:07
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825488
Фотография SomewhereSomehow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SomewhereSomehow
Участник
Змей,

Не смотря на то, что по факту вы правы, вы оказываете ТС медвежью услугу, думаю, Glory намекает именно на это.
...
Рейтинг: 0 / 0
04.12.2014, 20:36
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825950
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
Проблема легко решается без всяких SEТ`ов
Код: sql
1.
select * from #z1 where ni not in (select ni from #t1 where ni is not NULL)



Просто надо быть в курсе что есть такая багофича и учитывать. Теперь я в курсе.
...
Рейтинг: 0 / 0
05.12.2014, 11:32
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825962
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
Dima TПросто надо быть в курсе что есть такая багофича и учитывать.
Это багофича называется троичная логика.
...
Рейтинг: 0 / 0
05.12.2014, 11:36
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38825985
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Сия тема дает важный урок - прежде, чем строить предположения и делать заключения, необходимо собрать по вопросу как можно больше информации.

not exists(), кстати, гарантирует результат без знания о NULLABLE сравниваемого значения.
...
Рейтинг: 0 / 0
05.12.2014, 11:45
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38826009
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Dima TПроблема легко решается без всяких SEТ`ов
Код: sql
1.
select * from #z1 where ni not in (select ni from #t1 where ni is not NULL)




Просто надо быть в курсе что есть такая багофича и учитывать. Теперь я в курсе.Владислав прав: надо всегда использовать NOT EXISTS вместо NOT IN.
...
Рейтинг: 0 / 0
05.12.2014, 11:59
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38826018
Dima T
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dima T
Участник
JeStoneDima T,
Все дело в особенностях троичной логики.
Почему так происходит даже на хабре писали. Вот ссылочка
Раздел "Операторы IN и NOT IN" с разложением на бинарное дерево
Почитал, ясности не добавило. Там просто констатация факта: "IN выберет совпавшие кроме NULL, NOT IN ничего не выберет"
Про троичную в курсе, но нестыковка получается:
для выборки с IN, например 1 in (1, NULL) , два результата сравнения: есть и неизвестно - включаем 1 в результат.
для выборки с NOT IN, например 2 not in (1, NULL) , два результата сравнения: нет и неизвестно - не включаем 2 в результат.
Чем "есть" отличается "нет"?

ИМХУ данное поведение просто кем-то надумано и заявлено как аксиома.
...
Рейтинг: 0 / 0
05.12.2014, 12:10
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38826035
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
Dima TЧем "есть" отличается "нет"?
bol - IN
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
...
Рейтинг: 0 / 0
05.12.2014, 12:16
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38826036
Фотография daw
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daw
Участник
Dima TПро троичную в курсе, но нестыковка получается:
для выборки с IN, например 1 in (1, NULL) , два результата сравнения: есть и неизвестно - включаем 1 в результат.
для выборки с NOT IN, например 2 not in (1, NULL) , два результата сравнения: нет и неизвестно - не включаем 2 в результат.
Чем "есть" отличается "нет"?

в случае с IN мы точно знаем, что одно из сравнений истинно. этого уже достаточно, чтобы сказать, что условие истинно. true OR unknown = true.
в случае с NOT IN мы точно знаем, что один из результатов - нет, а вот при сравнении с NULL результат получается "неизвестно". поэтому, никаких оснований считать результат истинным нет. false AND unknown = unknown.
...
Рейтинг: 0 / 0
05.12.2014, 12:16
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38826046
Glory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Glory
Участник
Dima TИМХУ данное поведение просто кем-то надумано и заявлено как аксиома.
Потому что вы опять из троичной логики пытатесь сделать двоичную
2 not in NULL - это не "истина" и не "ложь". Это "неизвестно"
...
Рейтинг: 0 / 0
05.12.2014, 12:19
| Ответить | Цитировать | Написать  
where not in (...) и NULL. Почему так?
    #38826053
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Dima T,

на данном форуме, кстати, есть множество тем по этому поводу.
Вот, например, вспоминаю те, в которых сам участвовал:
Не работает ... where in (select a ....
Неожиданное поведение NOT IN с пустыми значениеми (NULL)
...
Рейтинг: 0 / 0
05.12.2014, 12:25
| Ответить | Цитировать | Написать  
25 сообщений из 34, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / where not in (...) и NULL. Почему так?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=38825001&tid=1699740]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 57ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 53ms
get tp. blocked users:
 2ms
others: 11ms
total:  164ms
созд. / загр.: 164ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]