IS NOT NULL и ISNULL (str, NULL) в предложении WHERE

У меня есть три таблицы (упрощенно здесь):

recipients: recipientId, isGroup
users: userId, first name, last name
groups: groupid, groupname

Я хочу восстановить имя/фамилию, если получатель является пользователем, и имя группы, если получатель является группой. Возможно, что получатель не является ни тем, ни другим (т. Е. Группа, которая была удалена / больше не существует), поэтому в этом случае я не хочу ничего возвращать.

Итак, вот что я сделал:

select u.first_name, u.last_name, g.groupname, r.isGroup
from recipients r
left join users u on u.userid = r.recipientId
left join groups g on g.groupid = r.recipientId
where r.isGroup IS NULL or g.groupname IS NOT NULL

Приведенный выше запрос не возвращает ожидаемых результатов. Я получаю это обратно:

adam, smith, null, null
yolanda, smith, null, null
null, null, members, 1
null, null, null, 1

Последняя строка является неожиданной, так как явно отсутствует имя группы (g.groupname равно NULL) и r.IsGroup = 1.

Когда я делаю это:

select u.first_name, u.last_name, g.groupname, r.isGroup
from recipients r
left join users u on u.userid = r.recipientId
left join groups g on g.groupid = r.recipientId
where r.isGroup IS NULL

Я получаю ожидаемые результаты:

adam, smith, null, null
yolanda, smith, null, null

И когда я делаю это:

select u.first_name, u.last_name, g.groupname, r.isGroup
from recipients r
left join users u on u.userid = r.recipientId
left join groups g on g.groupid = r.recipientId
where g.groupname IS NOT NULL

Я получаю ожидаемые результаты:

null, null, members, 1

Только когда я объединяю два предложения where с оператором OR, я получаю дополнительную строку.

Теперь, когда я изменяю свой запрос на (изменение с IS NULL на ISNULL):

select u.first_name, u.last_name, g.groupname, r.isGroup
from recipients r
left join users u on u.userid = r.recipientId
left join groups g on g.groupid = r.recipientId
where r.isGroup IS NULL or ISNULL(g.groupname, null) IS NOT NULL

Я получаю ожидаемые результаты:

adam, smith, null, null
yolanda, smith, null, null
null, null, members, 1

На самом деле мне даже не нужно менять предложение where, следующий запрос работает так же хорошо и дает мне ожидаемый результат, показанный выше:

select u.first_name, u.last_name, ISNULL(g.groupname,null), r.isGroup
from recipients r
left join users u on u.userid = r.recipientId
left join groups g on g.groupid = r.recipientId
where r.isGroup IS NULL or g.groupname IS NOT NULL

ТАК, вопрос, ЗАЧЕМ? Почему добавление ISNULL в оператор SELECT меняет работу предложения WHERE? Почему это вообще имеет значение? Почему мой первый запрос не работает? Почему он не работает только тогда, когда я добавляю ИЛИ? Почему он не работает и без него?

Заранее спасибо.

Я использую MS SQL Server 2008.

правки: исправлена ​​опечатка, уточнен вопрос


sql sql-server isnull
person Swati    schedule 14.09.2011    source источник
comment
Можете ли вы убедиться, что g.groupname содержит значение NULL вместо строки, такой как NULL. Иногда это может сбить вас с толку.   -  person Hong Ning    schedule 14.09.2011
comment
да. Я убедился. Кроме того, если бы это было не так, ISNULL(g.groupname, null) IS NOT NULL не имело бы никакого значения.   -  person Swati    schedule 14.09.2011

Ответы (5)


arrow_upward
4
arrow_downward

Мы обнаружили, что это проблема в Sql Server 2008 без установленных пакетов обновлений. Попробуйте установить SP1 или SP2. В нашем случае пакет обновления решил проблему.

person ssiscott    schedule 22.09.2011

arrow_upward
2
arrow_downward

ISNULL — это функция, которая повторно настраивает указанное входное значение, если столбец имеет значение null, в противном случае — значение столбца.

ISNULL(tbl.x, 'y') --returns 'y' if tbl.x is null otherwise the value of tbl.x
person Magnus    schedule 14.09.2011
comment
Тогда это эквивалентно: ISNULL(g.groupname, null) IS NOT NULL и g.groupName IS NOT NULL, верно? Поскольку, когда g.groupname имеет значение null, ISNULL разрешается в значение null. Однако мои запросы указывают, что это не эквивалентно. - person Swati; 14.09.2011

arrow_upward
2
arrow_downward

Я полагаю, что вы пытаетесь получить всех пользователей и группы, где, если это пользователь, first_name и last_name будут чем-то, а имя группы будет NULL, но если это группа, first_name и last_name будут NULL, а имя группы будет чем-то . Вместо этого ваше предложение WHERE фильтрует результаты, а не определяет условия соединения ваших данных. Я думаю, это даст то, что вы ищете (при условии, что r.isGroup == 1 означает, что это группа):

Чтобы использовать более или менее исходный запрос, но исключить записи, которые не существуют как группа или пользователь:

select u.first_name, u.last_name, g.groupname, r.isGroup
from recipients r
left join users u 
    on u.userid = r.recipientId and r.isGroup != 1
left join groups g 
    on g.groupid = r.recipientId and r.isGroup == 1
where u.userid IS NOT NULL or g.groupid IS NOT NULL

Однако я думаю, что объединение с внутренними соединениями будет работать лучше:

select u.first_name, u.last_name, NULL, r.isGroup
from recipients r
inner join users u on u.userid = r.recipientId
where r.isGroup != 1
union
select NULL, NULL, g.groupname, r.isGroup
from recipients r
inner join groups g on g.groupid = r.recipientId
where r.isGroup == 1
person six8    schedule 14.09.2011
comment
Возможно, что даже когда r.IsGroup = 1, это не группа (или что-то в этом роде). Я хочу опустить результаты, которые не найдены ни в одной из таблиц, что происходит только тогда, когда r.IsGroup = 1 и его нет в таблице групп (происходит, потому что группы могут быть удалены). Я не могу этого сделать. - person Swati; 14.09.2011
comment
Спасибо. Но я уже знаю, как получить желаемый результат, мне просто интересно, почему мой первый запрос не работает, а второй нет - и единственная разница заключается в использовании ISNULL. Кроме того, ваш первый запрос дает мне ту же самую проблему, что и мой исходный запрос. Это не работает и возвращает лишнюю строку, которую нужно было отфильтровать. - person Swati; 15.09.2011
comment
Это странно. Либо это какой-то артефакт SQL-сервера, вызывающий странное поведение, либо данные не такие, как вы ожидаете. Вы можете добавить u.userid и g.groupid к результату выбора, чтобы убедиться, что они соответствуют ожидаемым значениям. С вашими запросами у вас есть большой риск непредвиденных результатов, если у вас есть groupids и userids, которые перекрываются, если вы не обрабатываете это условие в предложении on. - person six8; 15.09.2011
comment
Вы также можете изменить where u.userid IS NOT NULL or g.groupid IS NOT NULL на where (r.isGroup != 1 and u.userid IS NOT NULL) or (r.isGroup == 1 and g.groupid IS NOT NULL), но в этом нет необходимости, если данные соответствуют ожиданиям, и это будет работать ужасно. Извините, я не могу решить проблему ISNULL напрямую, так как кажется, что есть проблема в другом месте, вызывающая неожиданные результаты. - person six8; 15.09.2011
comment
Идентификаторы групп и идентификаторы пользователей никогда не перекрываются. Никогда. Я совершенно уверен, что данные соответствуют моим ожиданиям, иначе ISNULL тоже не сработает :( - person Swati; 15.09.2011
comment
Я тоже это сделал. По отдельности они оба дают мне ожидаемые результаты. Итак, если (r.isGroup != 1 and u.userid IS NOT NULL) дает мне 2 результата, а (r.isGroup == 1 and g.groupid IS NOT NULL) дает мне 3 результата, ИЛИ из них дает мне 9 результатов вместо ожидаемых 5. Я получаю результаты в дополнение к тому, что я получил бы в противном случае. Понятия не имею почему. Как будто он полностью игнорирует предложение WHERE. - person Swati; 15.09.2011

arrow_upward
0
arrow_downward

Из предложения SELECT:

u.имя группы

из предложения WHERE:

g.имя группы

Сделайте эти два совпадения и сообщите, если увидите что-то другое.

person Joel Coehoorn    schedule 14.09.2011
comment
О, извините, опечатка. Предполагается, что это также будет g.groupname в предложении SELECT. - person Swati; 14.09.2011

arrow_upward
0
arrow_downward

Это, кажется, дает желаемые результаты, хотя, если есть какие-либо ошибки от NULLS, швейцарского сырения ваших данных, это может не сработать:

select u.first_name, u.last_name, g.groupname, r.isGroup
from #recipients r
left join #users u on u.userid = r.recipientId
left join #groups g on g.groupid = r.recipientId
WHERE  g.groupname IS NOT NULL 
    OR ISNULL(r.IsGroup,0) > CASE WHEN g.groupid IS NOT NULL AND g.groupname IS NULL THEN -1 ELSE 0 END
person Wil    schedule 14.09.2011