Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / LEFT JOIN + WHERE + Null / 14 сообщений из 14, страница 1 из 1
04.12.2019, 12:32
    #39898032
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
Здравствуйте!

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

Есть такой SQL-запрос.
 
SQL-запрос 1
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
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 name22019-01-01 1 1 1 Элемент1 Элемент12019-02-01 2 2 1 Элемент2 Элемент22019-03-01 3 1 1 Элемент3 Элемент12019-04-01 4 2 1 Элемент4 Элемент22019-05-01 NULL 1 1 NULL Элемент12019-06-01 2 2 1 Элемент2 Элемент22019-07-01 NULL 1 1 NULL Элемент12019-08-01 1 1 1 Элемент1 Элемент12019-09-01 NULL 1 1 NULL Элемент12019-10-01 2 NULL 1 Элемент2 NULL2019-11-01 NULL NULL 1 NULL NULL2019-12-01 1 2 1 Элемент1 Элемент2
Нужно получить данные, где mark1_id не равны 3 и 4, а mark2_id не равен 2. Добавляем фильтр
 
SQL-запрос 2
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
WHERE
	(
		(T2.[mark_id] <> 3
		AND
		T2.[mark_id] <> 4)
	AND
		T3.[mark_id] <> 2
	)

то получаем
date mark1_id mark2_id cost name1 name22019-01-01 1 1 1 Элемент1 Элемент12019-08-01 1 1 1 Элемент1 Элемент1
Обращаем внимание на то, что отсекаются строки с Null. А их не надо отсекать.

Если так написать
 
SQL-запрос 3
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
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 name22019-01-01 1 1 1 Элемент1 Элемент12019-05-01 NULL 1 1 NULL Элемент12019-07-01 NULL 1 1 NULL Элемент12019-08-01 1 1 1 Элемент1 Элемент12019-09-01 NULL 1 1 NULL Элемент12019-10-01 2 NULL 1 Элемент2 NULL2019-11-01 NULL NULL 1 NULL NULL
Результат верный.

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

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

2. Данный пример является простым. На практике соединяются множество таблиц и важно, чтобы не потерять null для каждого признака (в данном случае mark1_id и mark2_id ). Скажите, какие есть варианты записи в WHERE более оптимально?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 12:46
    #39898038
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
ferzmikk,

Код: sql
1.
2.
3.
4.
WHERE
	isnull(T2.[mark_id], 0) not in (3, 4)
	AND
	isnull(T3.[mark_id], 0) <> 2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 12:51
    #39898041
Тройка
Тройка
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
ferzmikk

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

Не совсем, ничего автоматические не отсекается. Просто Null не равно 2 и не равно 3 или 4. И даже Null не равен Null
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 12:54
    #39898046
a_voronin
a_voronin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
invm
ferzmikk,

Код: sql
1.
2.
3.
4.
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 может понизить производительность .
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 13:05
    #39898055
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
a_voronin
invm
ferzmikk,

Код: sql
1.
2.
3.
4.
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 может понизить производительность .
Так, результат тогда будет не тот, что нужен ТС
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select 1 where null not in (3,4)
select 1 where isnull(null,0) not in (3,4)

-----------

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


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

(затронута одна строка)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 13:05
    #39898057
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
a_voronin
Я бы ограничился T2.[mark_id] not in (3, 4).
Код: sql
1.
select 1 where null not in (1, 2);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 13:19
    #39898066
a_voronin
a_voronin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
invm
a_voronin
Я бы ограничился T2.[mark_id] not in (3, 4).
Код: sql
1.
select 1 where null not in (1, 2);



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


-SQL-запрос 3

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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
		)
	)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 14:36
    #39898110
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
Тройка,

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

Неверно мыслите, null - это неизвестное значение, результат сравнения детерминированного с неизвестным неизвестен. Равно как и неизвестного с неизвестным также неизвестен. Чтобы неизвестные прошли сравнение их надо сделать детерминированными, т.е. присвоить значения, которые можно сравнивать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 14:52
    #39898129
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
a_voronin
Я не это имел ввиду. Я имел ввиду вот это
Конечно.
Цитировали меня и писали о isnull, а оказалось, что вовсе не про мой ответ и не о isnull. Забавно.

И даже если так, ваша рекомендация от этого правильнее не стала.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 15:00
    #39898134
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
invm
ferzmikk,

Код: sql
1.
2.
3.
4.
WHERE
	isnull(T2.[mark_id], 0) not in (3, 4)
	AND
	isnull(T3.[mark_id], 0) <> 2

Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 15:04
    #39898139
a_voronin
a_voronin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
invm
a_voronin
Я не это имел ввиду. Я имел ввиду вот это
Конечно.
Цитировали меня и писали о isnull, а оказалось, что вовсе не про мой ответ и не о isnull. Забавно.

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


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

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

Код: sql
1.
2.
3.
4.
WHERE
	isnull(T2.[mark_id], 0) not in (3, 4)
	AND
	isnull(T3.[mark_id], 0) <> 2


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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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
		)
	)



?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 15:45
    #39898171
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
a_voronin
1) Это было про ваш ответ
2) Учить меня работе с NULL не надо
То про мой, то не про мой. Вас не понять.
И, судя по вашему "замечательному" ответу, учить работе с null таки надо. Только вот, как показывает практика, бестолку...

a_voronin
Вы хотите сказать, что это
...
По скорости не уступает вот этому
...
Зависит от количества null'ов
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
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

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 16:46
    #39898214
Shakill
Shakill
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
invm,

туда же :)
Код: sql
1.
select @c = count(*) from dbo.t where not exists(select a intersect (select 1 union all select 2)) option (maxdop 1);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2019, 18:18
    #39898275
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LEFT JOIN + WHERE + Null
Shakill,

Чемпионом не станет, ибо будет NL + Constant Scan.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / LEFT JOIN + WHERE + Null / 14 сообщений из 14, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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