Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 LEFT JOIN + WHERE + Null  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
Здравствуйте!

Есть @Table1. В этой таблице есть поля mark1_id и mark2_id, которые соответственно ссылаются на справочные таблицы @Table2 и @Table3.

Есть такой SQL-запрос.
+SQL-запрос 1
DECLARE @Table1 table(
	[date] date,
	[mark1_id] int,
	[mark2_id] int,	
	[cost] int
)
;

INSERT INTO
  @Table1 
VALUES 
('01.01.2019', 1,       1,       1),
('01.02.2019', 2,       2,       1),
('01.03.2019', 3,       1,       1),
('01.04.2019', 4,       2,       1),
('01.05.2019', null,    1,       1),
('01.06.2019', 2,       2,       1),
('01.07.2019', null,    1,       1),
('01.08.2019', 1,       1,       1),
('01.09.2019', null,    1,       1),
('01.10.2019', 2,       null,    1),
('01.11.2019', null,    null,    1),
('01.12.2019', 1,       2,       1)
;

DECLARE @Table2 table(	
	[mark_id] int,
	[name] nvarchar(200)
)
;

INSERT INTO
  @Table2 
VALUES 
(1,'Элемент1'),
(2,'Элемент2'),
(3,'Элемент3'),
(4,'Элемент4')

DECLARE @Table3 table(	
	[mark_id] int,
	[name] nvarchar(200)
)
;

INSERT INTO
  @Table3 
VALUES 
(1,'Элемент1'),
(2,'Элемент2')

SELECT
	T1.[date],
	T1.[mark1_id],
	T1.[mark2_id],	
	T1.[cost],
	T2.[name] AS name1,
	T3.[name] AS name2
FROM
	@Table1 AS T1
LEFT JOIN
	@Table2 AS T2
ON
	T1.[mark1_id] = T2.[mark_id]
LEFT JOIN
	@Table3 AS T3
ON
	T1.[mark2_id] = T3.[mark_id]
который возвращает такой результат
date mark1_id mark2_id cost name1 name2
2019-01-01 1 1 1 Элемент1 Элемент1
2019-02-01 2 2 1 Элемент2 Элемент2
2019-03-01 3 1 1 Элемент3 Элемент1
2019-04-01 4 2 1 Элемент4 Элемент2
2019-05-01 NULL 1 1 NULL Элемент1
2019-06-01 2 2 1 Элемент2 Элемент2
2019-07-01 NULL 1 1 NULL Элемент1
2019-08-01 1 1 1 Элемент1 Элемент1
2019-09-01 NULL 1 1 NULL Элемент1
2019-10-01 2 NULL 1 Элемент2 NULL
2019-11-01 NULL NULL 1 NULL NULL
2019-12-01 1 2 1 Элемент1 Элемент2

Нужно получить данные, где mark1_id не равны 3 и 4, а mark2_id не равен 2. Добавляем фильтр
+SQL-запрос 2
WHERE
	(
		(T2.[mark_id] <> 3
		AND
		T2.[mark_id] <> 4)
	AND
		T3.[mark_id] <> 2
	)
то получаем
date mark1_id mark2_id cost name1 name2
2019-01-01 1 1 1 Элемент1 Элемент1
2019-08-01 1 1 1 Элемент1 Элемент1

Обращаем внимание на то, что отсекаются строки с Null. А их не надо отсекать.

Если так написать
+SQL-запрос 3
WHERE	
	(
		(		
			(
				T2.[mark_id] <> 3
				AND
				T2.[mark_id] <> 4
			)
			OR
				T2.[mark_id] is Null
		)
		AND
		(
			T3.[mark_id] <> 2
			OR
			T3.[mark_id] is Null
		)
	)
получаем
date mark1_id mark2_id cost name1 name2
2019-01-01 1 1 1 Элемент1 Элемент1
2019-05-01 NULL 1 1 NULL Элемент1
2019-07-01 NULL 1 1 NULL Элемент1
2019-08-01 1 1 1 Элемент1 Элемент1
2019-09-01 NULL 1 1 NULL Элемент1
2019-10-01 2 NULL 1 Элемент2 NULL
2019-11-01 NULL NULL 1 NULL NULL

Результат верный.

Но, все таки есть вопросы.

1. Я правильно понимаю, что при фильтрации (SQL-запрос 2), значения Null автоматически отсекаются?

2. Данный пример является простым. На практике соединяются множество таблиц и важно, чтобы не потерять null для каждого признака (в данном случае mark1_id и mark2_id). Скажите, какие есть варианты записи в WHERE более оптимально?
4 дек 19, 12:32    [22031943]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
invm
Member

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

WHERE
	isnull(T2.[mark_id], 0) not in (3, 4)
	AND
	isnull(T3.[mark_id], 0) <> 2
4 дек 19, 12:46    [22031955]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
Тройка
Member

Откуда:
Сообщений: 87
ferzmikk

1. Я правильно понимаю, что при фильтрации (SQL-запрос 2), значения Null автоматически отсекаются?

Не совсем, ничего автоматические не отсекается. Просто Null не равно 2 и не равно 3 или 4. И даже Null не равен Null
4 дек 19, 12:51    [22031959]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4374
invm
ferzmikk,

WHERE
	isnull(T2.[mark_id], 0) not in (3, 4)
	AND
	isnull(T3.[mark_id], 0) <> 2


Я бы ограничился T2.[mark_id] not in (3, 4). Ибо ISNULL может понизить производительность .
4 дек 19, 12:54    [22031964]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
court
Member

Откуда:
Сообщений: 1956
a_voronin
invm
ferzmikk,

WHERE
	isnull(T2.[mark_id], 0) not in (3, 4)
	AND
	isnull(T3.[mark_id], 0) <> 2



Я бы ограничился T2.[mark_id] not in (3, 4). Ибо ISNULL может понизить производительность .
Так, результат тогда будет не тот, что нужен ТС
select 1 where null not in (3,4)
select 1 where isnull(null,0) not in (3,4)

-----------

(затронуто строк: 0)


-----------
1

(затронута одна строка)
4 дек 19, 13:05    [22031980]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
a_voronin
Я бы ограничился T2.[mark_id] not in (3, 4).
select 1 where null not in (1, 2);
4 дек 19, 13:05    [22031982]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4374
invm
a_voronin
Я бы ограничился T2.[mark_id] not in (3, 4).
select 1 where null not in (1, 2);


Я не это имел ввиду. Я имел ввиду вот это


-SQL-запрос 3

WHERE	
	(
		(		
				T2.[mark_id] not in (3, 4)
			OR
				T2.[mark_id] is Null
		)
		AND
		(
			T3.[mark_id] <> 2
			OR
			T3.[mark_id] is Null
		)
	)
4 дек 19, 13:19    [22032004]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7383
Тройка,

автор
Просто Null не равно 2 и не равно 3 или 4. И даже Null не равен Null


Неверно мыслите, null - это неизвестное значение, результат сравнения детерминированного с неизвестным неизвестен. Равно как и неизвестного с неизвестным также неизвестен. Чтобы неизвестные прошли сравнение их надо сделать детерминированными, т.е. присвоить значения, которые можно сравнивать.
4 дек 19, 14:36    [22032103]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
a_voronin
Я не это имел ввиду. Я имел ввиду вот это
Конечно.
Цитировали меня и писали о isnull, а оказалось, что вовсе не про мой ответ и не о isnull. Забавно.

И даже если так, ваша рекомендация от этого правильнее не стала.
4 дек 19, 14:52    [22032130]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
ferzmikk
Member

Откуда:
Сообщений: 2029
invm
ferzmikk,

WHERE
	isnull(T2.[mark_id], 0) not in (3, 4)
	AND
	isnull(T3.[mark_id], 0) <> 2
Спасибо!
4 дек 19, 15:00    [22032137]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4374
invm
a_voronin
Я не это имел ввиду. Я имел ввиду вот это
Конечно.
Цитировали меня и писали о isnull, а оказалось, что вовсе не про мой ответ и не о isnull. Забавно.

И даже если так, ваша рекомендация от этого правильнее не стала.


1) Это было про ваш ответ
2) Учить меня работе с NULL не надо

Вы хотите сказать, что это

WHERE
	isnull(T2.[mark_id], 0) not in (3, 4)
	AND
	isnull(T3.[mark_id], 0) <> 2

По скорости не уступает вот этому

WHERE	
	(
		(		
				T2.[mark_id] not in (3, 4)
			OR
				T2.[mark_id] is Null
		)
		AND
		(
			T3.[mark_id] <> 2
			OR
			T3.[mark_id] is Null
		)
	)


?
4 дек 19, 15:04    [22032145]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
a_voronin
1) Это было про ваш ответ
2) Учить меня работе с NULL не надо
То про мой, то не про мой. Вас не понять.
И, судя по вашему "замечательному" ответу, учить работе с null таки надо. Только вот, как показывает практика, бестолку...

a_voronin
Вы хотите сказать, что это
...
По скорости не уступает вот этому
...
Зависит от количества null'ов
+
use tempdb;
go

create table dbo.t (a int null, b int null, c char(500) null);
insert into dbo.t
 (a, b)
 select top (3000000)
  null, 3 
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
go

declare @c int;

set statistics time on;
select @c = count(*) from dbo.t where isnull(a, 0) not in (1, 2) option (maxdop 1);
select @c = count(*) from dbo.t where a is null or a not in (1, 2) option (maxdop 1);
set statistics time off;

set statistics time on;
select @c = count(*) from dbo.t where isnull(b, 0) not in (1, 2) option (maxdop 1);
select @c = count(*) from dbo.t where b is null or b not in (1, 2) option (maxdop 1);
set statistics time off;
go

drop table dbo.t;
go
4 дек 19, 15:45    [22032204]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
Shakill
Member

Откуда: мск
Сообщений: 1870
invm,

туда же :)
select @c = count(*) from dbo.t where not exists(select a intersect (select 1 union all select 2)) option (maxdop 1);
4 дек 19, 16:46    [22032297]     Ответить | Цитировать Сообщить модератору
 Re: LEFT JOIN + WHERE + Null  [new]
invm
Member

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

Чемпионом не станет, ибо будет NL + Constant Scan.
4 дек 19, 18:18    [22032410]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить