SqlCmd все о SQL технологиях.





Все что необходимо для изучения и работы с СУБД Microsoft SQL Server, MySQL, MariaDB, MongoDB. Авторские статьи, библиотека фрагментов T-SQL кода, сборник полезных инструментов.



MS SQL Server, SQL Server, T-SQL, исходники, сниппеты, статьи, учебники SQL, утилиты SQL, инструменты SQL



Header RSS-подписка на обновления сайта eMail-подписка на обновления сайта

To NULL or NOT to NULL? К вопросу о троичной логике. Часть 2/2.





  • Другие части статьи:
  • 1
  • 2
  • вперед »

UNKNOWN и различные конструкции языка T-SQL.

Итак, в основном разделе статьи мы рассмотрим вопрос: как различные элементы языка T-SQL «выкручиваются» обходя этот «неудобный» результат UNKNOWN и подменяя его то FALSE, то TRUE, а то и оставляя его «как есть». Делают они это подчас способами столь изощренными, что просто диву даешься. Ну а про то, что многообразие этих способов вносит ту самую «свежую струю» в жизнь 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
USE master
go
CREATE DATABASE [~DB~]
ON PRIMARY (
    NAME = '~DB~_Data',
    FILENAME = 'c:\sqlCMD.ru\~DB~.mdf',
    SIZE = 15 MB,
    MAXSIZE = 15 MB )
LOG ON (
    NAME = '~DB~_Log',
    FILENAME = 'c:\sqlCMD.ru\~DB~.ldf',
    SIZE = 15 MB,
    MAXSIZE = 15 MB )
GO
ALTER DATABASE [~DB~] SET PARAMETERIZATION SIMPLE
GO
USE [~DB~]
GO
create table T0 (Col1 int, Col2 char(2))
GO
create table T1 (Col1 int, Col2 char(2))
GO
create table T2 (ColID int UNIQUE)
GO
CREATE TABLE T3 (ColID int IDENTITY(1,1) NOT NULL, T2ID int NULL)
GO
ALTER TABLE T3 WITH CHECK ADD CONSTRAINT [FK_T3_T2] FOREIGN KEY(T2ID) REFERENCES T2(ColID)
GO
CREATE TABLE T4 (Col1 int CONSTRAINT myCHK1 CHECK(Col1 > 0), Col2 int CONSTRAINT myCHK2 CHECK(Col2 <> NULL))
GO
CREATE TABLE T5 (Col1 int UNIQUE, Col2 char(2))
GO
CREATE TABLE T6 (Col1 varchar(20), Col2 varchar(20), Col3 int)

Предполагается, что в начале каждого опыта все тестовые таблицы абсолютно пусты, хотя код их очистки для экономии места не приводится, поэтому не забывайте усекать тест-таблицы самостоятельно.

Клаузула WHERE.
1
2
3
4
5
6
7
8
9
USE [~DB~]
GO
insert T1 VALUES (22, 'ab')
insert T1 VALUES (44, NULL)
insert T1 VALUES (55, NULL)
insert T1 VALUES (88, 'ef')
GO
SELECT * FROM T1 WHERE Col2=NULL
SELECT * FROM T1 WHERE NOT(Col2=NULL)

Эта конструкция вызывает меньше всего вопросов и известна практически всем. Мы ее так же уже обсудили и приводим здесь лишь для полноты изложения. Клаузула WHERE «конвертирует» UNKNOWN в FALSE, а поэтому оба приведенных SELECT-а лишены смысла, они оба ни в коем случае и никогда не вернут ни единой строчки из таблицы. И уж конечно, программист написавший такое в расчете извлечь все строки у которых колонка Col2 содержит NULL/не-NULL значения лишь доказал, что он ни разу не посещал ресурс sqlCMD.ru. :roll:

Логический оператор [NOT] IN.
1
2
3
4
5
6
7
8
9
USE [~DB~]
GO
insert T1 VALUES (22, 'ab')
insert T1 VALUES (44, NULL)
insert T1 VALUES (55, NULL)
insert T1 VALUES (88, 'ef')
GO
SELECT * FROM T1 WHERE Col2 IN ('ab','ef', NULL)
SELECT * FROM T1 WHERE Col2 NOT IN ('tk','zy', NULL)

Чуть интересней обстоят дела, когда в той же клаузуле фигурирует логический оператор IN. Разберем два показанных SELECT-а последовательно.

Как известно ...col IN (a,b,c) есть просто короткая форма записи ...col=a OR col=b OR col=c. В нашем случае первый SELECT пытается отобрать строки для которых ...Col2='ab' OR Col2='ef' OR Col2=NULL. Обозначим промежуточный результат вычислений ...Col2='ab' OR Col2='ef' как X, тогда условие сокращается до X OR Col2=NULL, или, что очевидно, X OR UNKNOWN. Как мы знаем из таблицы сочетаний bool-значений последнее выражение будет просчитано в TRUE (и, как следствие, клаузула WHERE даст «добро» на включение очередной строки в резалт-сет) только и если только X будет тоже просчитан в TRUE. Отсюда видно, что от UNKNOWN не зависит ровным счетом ничего, а значит указание литерала NULL в логическом операторе IN синтаксически корректно, но лишено какого либо практического смысла. Вы не влияете этим включением на итоговый резалт-сет и уж, само собой, ни в коем случае не получите те строки где Col2 равно NULL.

Проверьте свои знания T-SQL: Переписать первый SELECT разбираемого примера так, что бы логический оператор IN продолжал в нем фигурировать, но возвращались и строки содержащие NULL в колонке Col2.

Смотреть ответ
SELECT * FROM T1 WHERE Col2 IN ('ab','ef') OR Col2 IS NULL

Со вторым SELECT-ом дела еще более интересные. Он, по непонятным пока причинам, не возвращает ни единой строки таблицы. :idea: Давайте для понимания картины изложим условие в клаузуле WHERE этого SELECT-а человеческим языком. Мы, очевидно, хотим видеть лишь те строки из таблицы T1 значения в колонке Col2 которых:

  • НЕ равны строке символов 'tk';
  • И НЕ равны строке символов 'zy'. Тут, на всякий случай, проконтролируйте вашего автора — точно Col2 должен быть «не равен» И «не равен»? Оператор IN, вообще-то, подразумевает OR, может все-таки «не равен» ИЛИ «не равен»?;
  • И НЕ равны NULL.

Переводим с «человечьего» на «T-SQL-ный»: Col2<>'tk' AND Col2<>'zy' AND Col2<>NULL. Пусть Col2<>'tk' AND Col2<>'zy' снова дают промежуточный результат X, тогда наше выражение сокращается до X AND Col2<>NULL, или что вновь очевидно, до X AND UNKNOWN. Согласно все той же таблицы сочетаний bool-значений последнее ни в коем случае и никогда не даст нам TRUE, а значит ни одна строка извлечена не будет. Что, собственно говоря, и наблюдается.

Проверьте свои знания T-SQL: Переписать второй SELECT разбираемого примера так, что бы логический оператор IN продолжал в нем фигурировать и возвращались все строки кроме содержащих в колонке Col2 значения 'tk', 'ab', NULL.

Смотреть ответ
SELECT * FROM T1 WHERE Col2 NOT IN ('tk','ab') AND Col2 IS NOT NULL
Клаузула GROUP BY.
1
2
3
4
5
6
7
8
9
10
11
USE [~DB~]
GO
insert T1 VALUES (2, 'ab')
insert T1 VALUES (4, NULL)
insert T1 VALUES (5, NULL)
insert T1 VALUES (8, 'ef')
insert T1 VALUES (3, 'ab')
insert T1 VALUES (1, NULL)
GO
SELECT  SUM(Col1) AS Sum, Col2 FROM T1
GROUP BY Col2

Тут особых комментариев не требуется. Как известно, теория говорит о полном и безоговорочном несовпадении любого NULL-значения с чем угодно, в том числе и с другим NULL-значением. Некоторые конструкции T-SQL (обсуждаемая клаузула в их числе) запросто обходят все эти теории и изящно приравнивают все NULL-ы друг другу. В их оправдание заметим, что интуитивно мы ждем именно такого поведения даже зная, что оно «ошибочно». Итого, группировка считает все NULL-ы в колонке по которой идет группировка равными и выделяет строки с этим значением в отдельную группу.

Клаузула GROUP BY с операторами ROLLUP, CUBE, GROUPING SETS.
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
USE [~DB~]
go
insert T6 VALUES ('Notebook','Dell',1)
insert T6 VALUES ('Desktop','Dell',2)
insert T6 VALUES ('Notebook','IBM',10)
insert T6 VALUES ('Desktop','IBM',20)
insert T6 VALUES ('Notebook',NULL,100)
insert T6 VALUES ('Desktop',NULL,200)
insert T6 VALUES (NULL,'Dell',1000)
insert T6 VALUES (NULL,'IBM',2000)
go
SELECT Col1, Col2, SUM(Col3) AS Total FROM T6 GROUP BY ROLLUP(Col1, Col2) ORDER BY Col1
SELECT Col1, Col2, SUM(Col3) AS Total FROM T6 GROUP BY CUBE(Col1, Col2) ORDER BY Col1

SELECT Col1, Col2, SUM(Col3) AS Total,
CASE
    WHEN GROUPING_ID(Col1, Col2)=0 THEN 'just row'
    WHEN GROUPING_ID(Col1, Col2)=2 THEN 'Total of: ' + ISNULL(col2, 'no-name vendor')
    WHEN GROUPING_ID(Col1, Col2)=1 THEN 'Total of: ' + ISNULL(col1, 'other equipment')
    WHEN GROUPING_ID(Col1, Col2)=3 THEN 'Total of::Grand Total::'
END AS Comment
FROM T6 GROUP BY ROLLUP(Col1, Col2) ORDER BY Comment

SELECT Col1, Col2, SUM(Col3) AS Total,
CASE
    WHEN GROUPING_ID(Col1, Col2)=0 THEN 'just row'
    WHEN GROUPING_ID(Col1, Col2)=2 THEN 'Total of: ' + ISNULL(col2, 'no-name vendor')
    WHEN GROUPING_ID(Col1, Col2)=1 THEN 'Total of: ' + ISNULL(col1, 'other equipment')
    WHEN GROUPING_ID(Col1, Col2)=3 THEN 'Total of::Grand Total::'
END AS Comment
FROM T6 GROUP BY CUBE(Col1, Col2) ORDER BY Comment

Как известно, три оператора указанных в заголовке текущего подраздела статьи, позволяют нам с вами «встраивать» прямо в итоговый резалт-сет различные комбинации итогов промежуточных, а так же выводить финальный «гранд-тотал». Так вот что касается обращений с NULL самих этих операторов, то тут полная аналогия с подразделом предыдущим — все NULL-ы считаются эквивалентными, образуют свою собственную «NULL-группу», и по этой-то группе проводятся те или иные агрегирующие вычисления (обыкновенно это будет простое суммирование). Однако тут добавляется проблема иного сорта, и преизрядная! Дело в том, что для индикации строки выходного резалт-сета содержащей не данные из таблицы-источника, а именно промежуточные/финальные итоги генерированные тремя обсуждаемыми операторами, SQL Server не придумал ничего умнее, как использовать все тот же NULL. Впрочем, выдвигать какие-то претензии в этом моменте будет некорректно, ведь группировка может пойти по колонке любого типа, а значит нам нужно нечто умеющее «вставлять себя» в любую колонку. Вы уже догадываетесь, что кандидатов на такую роль немного, а если быть точнее — один. В общем, увидев в резал-сете первого SELECT-а из приведенного чуть выше кода строку

Col1        Col2    Total
Notebook    NULL    111

мы оказывается в хорошей такой растерянности... Как это интерпретировать? Значит ли это, что у нас на складе всего 111шт. ноутбуков? Или у нас такое количество ноутбуков только от неизвестного (условно — «no-name», NULL) производителя? Аналогично и

Col1    Col2    Total
NULL    NULL    3000

А тут что? Всего 3 тыс. единиц любой компьютерной техники? Или это только 3 тыс. не ноутбуков и не десктопов (условно — прочее оборудование, NULL) от того же «no-name» производителя?

Второй из показанных SELECT-ов ставит перед нами не менее «мозговыносящие» вопросы. А для их решения нам, очевидно, требуется механизм способный различать строки «реальные» (т.е. физически содержащиеся в таблице), и строки генерированные. И такой механизм — есть! И даже целых два: функции GROUPING и GROUPING_ID. Персонально автор предпочитает второй вариант, как более универсальный и более продвинутый. Вы, разумеется, вольны пользоваться тем механизмом, что сочтете более удобным для себя. Как-бы там ни было, но SELECT-ы третий и четвертый из примера выше показывают применение именно функции GROUPING_ID. Результат такого применения очевиден: сложные вопросы, оказывается, могут иметь простые ответы.

Мы не разбираем работу указанных двух функций, так как такая задача была бы в стороне от целей текущей статьи. Кроме того, статьи BOL по двум указанным ссылкам справляются с тем же самым относительно неплохо, предоставляя и пояснения, и примеры работы обеих функций. НО! Не забывайте о неусыпном контроле читаемого вами! В первой части статьи текущей вы видели, что BOL пишут не «IT-боги», ошибаются (или, как минимум, применяют неудачные формулировки) и они. А вот что говорится в BOL-статье о функции GROUPING_ID: A lit bit indicates the corresponding argument is not a grouping column for the given output row. То бишь, в переводе: взведенный бит в битовой маске полученной как результат работы функции GROUPING_ID означает что соответствующая биту колонка не принимает участие в группировке данных для очередной строки резалт-сета. В то время как в реальности бит установленный в значение 1 (то есть как раз «взведенный») означает что соответствующая биту колонка именно что принимает участие в группировке. В общем — глаз да глаз нужен за этими «BOL-авцами». :x
Клаузула HAVING.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE [~DB~]
go
insert T1 VALUES (NULL, 'ab')
insert T1 VALUES (4, 'ef')
insert T1 VALUES (2, 'ef')
insert T1 VALUES (NULL, 'ab')
insert T1 VALUES (1, 'ef')
GO
SELECT  SUM(Col1) AS Sum, Col2 FROM T1
GROUP BY Col2
HAVING SUM(Col1)=NULL
GO
SELECT  SUM(Col1) AS Sum, Col2 FROM T1
GROUP BY Col2
HAVING SUM(Col1)<>NULL

Как известно, «HAVING — это почти как WHERE, но только для GROUP BY». В обсуждаемом нами ключе HAVING/WHERE абсолютно идентичны, и та и другая «засчитывают» UNKNOWN за FALSE. Таким образом, оба показанных SELECT-а полностью бессмысленны. Вы вовсе не отфильтровываете те группы суммы по Col1 которых равны/не равны NULL. Вместо этого вы просто заявляете — «хочу пустой резалт-сет», который и получаете незамедлительно.

Проверьте свои знания T-SQL: Переписать оба SELECT-а и реализовать правильную фильтрацию. То есть показывать лишь те группы строк (колонка группировки — Col2), сумма значений колонки Col1 которых равна/не равна NULL.

Смотреть ответ
1
2
3
4
5
6
7
SELECT  SUM(Col1) AS Sum, Col2 FROM T1
GROUP BY Col2
HAVING SUM(Col1) IS NULL
GO
SELECT  SUM(Col1) AS Sum, Col2 FROM T1
GROUP BY Col2
HAVING SUM(Col1) IS NOT NULL
Клаузула ORDER BY.
1
2
3
4
5
6
7
8
9
10
11
USE [~DB~]
GO
insert T1 VALUES (2, 'ab')
insert T1 VALUES (4, NULL)
insert T1 VALUES (5, NULL)
insert T1 VALUES (8, 'ef')
insert T1 VALUES (3, 'ab')
insert T1 VALUES (1, NULL)
GO
SELECT * FROM T1
ORDER BY Col2, Col1

Снова та же картина — все NULL-ы «как бы равны» друг другу, что полностью опровергается теорией, но удобно на практике. Однако, здесь еще встает такой побочный вопрос: а кто «больше» — 'ab' или NULL? Ведь кто-то должен оказаться «сверху»? Опять же, теория дает вполне четкий ответ на этот вопрос — UNKNOWN. Как можно сказать кто/что больше, строка из двух символов или неизвестность обозначаемая NULL-ом? Практика, в свою очередь, запросто решает даже и не такие иезуитские вопросы, а в данном случае она постановляет: любое NULL-значение «условно меньше» любого не-NULL-значения. Так что в итоговом резалт-сете пойдут сначала NULL-ы (причем между ними никакой сортировки не будет, они все «равны»), потом 'ab', в конце 'ef'.

Проверьте свои знания T-SQL: Переписать показанный SELECT так, что бы сортировка шла сначала по Col2 (по возрастанию), а среди одинаковых значений этой колонки по значениям колонки Col1 (по возрастанию). Собственно, эти два условия уже выполнены в последнем коде. Считать что любой NULL больше любого не-NULL-а. А вот это вам нужно придумать как реализовать. Не расстраивайтесь если у вас не получится взять вопрос «с ходу», он действительно много сложнее, чем кажется на первый взгляд и определенно требует нестандартного «SQL-мышления».

Смотреть ответ
SELECT * FROM T1 ORDER BY (CASE WHEN Col2 IS NULL THEN 'zzzz' ELSE Col2 END), Col2, Col1. Вместо 'zzzz' нужно/можно поставить любую комбинацию символов которая гарантированно «больше» любого не-NULL значения колонки Col2. Вы спрашиваете «а если бы колонка Col2 имела числовой тип»? Все тоже самое, только 'zzzz' следовало бы заменить любым «ну очень большим» числом, к примеру для типа int отлично подошло бы 2 147 483 647.
Смотреть альтернативный ответ
Посетитель блога invm подсказал более универсальный вариант решения поставленной задачи, позволяющей вам абстрагироваться от типа колонки Col2 и подбора для нее «достаточно большого» значения: SELECT * FROM T1 ORDER BY (CASE WHEN Col2 IS NULL THEN 1 ELSE 0 END), Col2, Col1. Отличная альтернатива! 8)
Операторы UNION, EXCEPT и INTERSECT.
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
USE [~DB~]
go
insert T0 VALUES (1, 'bc')
insert T0 VALUES (4, NULL)
insert T0 VALUES (5, NULL)
insert T0 VALUES (8, 'ef')
GO
insert T1 VALUES (1, 'ab')
insert T1 VALUES (4, NULL)
insert T1 VALUES (5, NULL)
insert T1 VALUES (8, 'ef')
insert T1 VALUES (2, 'ab')
insert T1 VALUES (2, NULL)
GO
SELECT * FROM T0
UNION
SELECT * FROM T1
GO
SELECT * FROM T0
EXCEPT
SELECT * FROM T1
GO
SELECT * FROM T0
INTERSECT
SELECT * FROM T1

Тоже все весьма прозрачно, все три оператора работы с наборами считают что NULL=NULL есть истина и от этого «пляшут». Мы, разумеется, не будем разбирать работу каждого оператора, предполагается что читатель осведомлен в этом вопросе. Для нас важно лишь, что эти операторы снова обращаются с NULL-ом не так как предписывает теория, а так как мы интуитивно ожидаем. Например EXCEPT оставит из таблицы T0 лишь строчку 1/'bc', так как остальные три строки он успешно обнаружит в таблице T1.

Соединение таблиц.

Если вы уже стали подумывать «а нельзя было просто написать, что сравнение NULL=NULL в теории дает UNKNOWN, а на практике — TRUE», то вот вам практический пример почему нельзя написать такого простого, красивого и сразу понятного правила (хоть и очень хочется):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE [~DB~]
go
insert T0 VALUES (1, 'bc')
insert T0 VALUES (3, NULL)
insert T0 VALUES (5, NULL)
insert T0 VALUES (7, 'ef')
insert T0 VALUES (9, '  ')
GO
insert T1 VALUES (0, '  ')
insert T1 VALUES (2, 'ab')
insert T1 VALUES (4, NULL)
insert T1 VALUES (6, NULL)
insert T1 VALUES (8, 'ef')
GO
SELECT T0.Col1, T1.Col1, T0.Col2 FROM T0 INNER JOIN T1 ON T0.Col2=T1.Col2
SELECT T0.Col1, T1.Col1, T0.Col2 FROM T0 INNER JOIN T1 ON T0.Col2<>T1.Col2

Уникальный случай! Впервые на практике сравнение NULL с NULL дает не истину, не ложь, а, страшно сказать, UNKNOWN!! Да, INNER JOIN тот редкий случай когда практика входит в полное согласие с теорией. Сопоставление двух NULL-ов не будет просчитано в TRUE ни по условию равенства между ними, ни даже по условию неравенства. Иными словами любая и каждая строчка содержащая NULL в колонке по которой идет объединение исключается из итогового резалт-сета вне зависимости от конкретного условия объединения. Если, разумеется, не предпринять дополнительных «телодвижений».

Проверьте свои знания T-SQL: Переписать показанный фрагмент кода так, что бы два NULL значения в колонке Col2 обеих таблиц так же условно считались равными и строки содержащие такие значения попадали бы в итоговый резалт-сет.

Смотреть ответ
SELECT T0.Col1, T1.Col1, T0.Col2 FROM T0 INNER JOIN T1 ON T0.Col2=T1.Col2 OR (T0.Col2 IS NULL AND T1.Col2 IS NULL)
Ключевое слово DISTINCT.
1
2
3
4
5
6
7
8
9
10
USE [~DB~]
go
insert T0 VALUES (1, 'bc')
insert T0 VALUES (3, NULL)
insert T0 VALUES (5, 'bc')
insert T0 VALUES (3, NULL)
insert T0 VALUES (5, NULL)
insert T0 VALUES (5, 'bc')
GO
SELECT DISTINCT * FROM T0

Вновь возвращаемся к классической схеме, NULL=NULL есть истина и DISTINCT успешно исключает из финального резалт-сета повторную строку таблицы со значениями 3/NULL (ну и 5/'bc' так же, это уж само-собой).

Проверьте свои знания T-SQL: Переписать показанный фрагмент кода так, что бы при исключении дублирующихся строк два значения NULL засчитывались бы как несовпадающие.

Смотреть ответ
1
2
3
SELECT * FROM T0 WHERE Col2 IS NULL
UNION ALL
SELECT DISTINCT * FROM T0 WHERE Col2 IS NOT NULL
Агрегатные функции.
1
2
3
4
5
6
7
8
9
10
11
12
USE [~DB~]
go
insert T0 VALUES (1, 'bc')
insert T0 VALUES (2, 'bc')
insert T0 VALUES (NULL, 'bc')
insert T0 VALUES (3, 'bc')
insert T0 VALUES (NULL, 'bc')
GO
SELECT MAX(Col1) FROM T0
SELECT SUM(Col1) FROM T0
SELECT COUNT(Col1) FROM T0
SELECT COUNT(*) FROM T0

Агрегатные функции, как мы с вами отметили еще во введении к статье, попросту «не видят» значений NULL, то есть игнорируют их. И поступают очень правильно. Потому что согласно строгой теории 1+2+NULL+3+NULL=NULL и второй SELECT должен, по хорошему, возвращать именно это значение. А интуитивно мы ждем нормальной суммы всех не-NULL значений из колонки Col1 — ее и получаем. Точно так же должна возвращать NULL и функция MAX, поскольку с «академической» точки зрения максимальное значение в той же колонке указать невозможно. А с практической — запросто, три, конечно. Единственное исключение из этого правила — функция COUNT при условии что в качестве аргумента ей указана «звездочка». Вот она ничего не игнорирует, а подсчитывает абсолютно все строки, пусть и содержащие во всех своих колонках NULL-ы. Но это, повторю, единственное исключение. Та же самая функция но с конкретным аргументом (например с именем столбца, как в примере) снова берет на вооружение общую концепцию агрегатных функций: «NULL? Пропускаем!».

Ограничение UNIQUE.
1
2
3
4
5
USE [~DB~]
go
insert T5 VALUES (NULL, 'ab')
insert T5 VALUES (4, 'ef')
insert T5 VALUES (NULL, 'cd')

Как должно быть известно всем, колонка с означенным в данном подразделе статьи ограничением позволяет среди всех своих значений ровно один NULL. Попытка вставить в ту же колонку второе значение NULL приводит к Violation of UNIQUE KEY constraint. Поэтому в некотором смысле можно считать что SQL Server при реализации обсуждаемого ограничения рассматривает два NULL значения как идентичные. А можно считать и так: согласно ANSI-стандарту ограничение UNIQUE должно контролировать только не-NULL значения и полностью игнорировать NULL-ы. Иными словами в UNIQUE-колонке должно быть позволено сколько угодно таких значений. Своя «сермяжная правда» в таком взгляде на проблему есть — ведь, строго говоря, два (а так же три, четыре, ..., сколько угодно) NULL-а не равны между собой, а следовательно уникальность выдерживается. С другой стороны это будет довольно странная уникальность строк, когда при запросе типа ...IS NULL вам будет возвращаться не одна строка (или даже ни одной), а целая куча. В общем — вопрос спорный. Если вы сторонник «чистоты теорий» и «стандарто-ориентированности» продуктов и считаете что нынешняя реализация данного ограничения некорректна, вы можете оставить свой голос в поддержку изменения реализации, так что бы она стала «ANSI-standard». На момент написания данных строк 186 человек ратуют за изменения и лишь 13 устраивает текущее положение дел (кстати, по ссылке можно ставить свой голос не только «за», но и «против», если вы считаете что «и так все нормально»). Возможно со временем разработчики SQL Server и прислушаются к этому подавляющему большинству, хотя для этого однозначно придется ввести опцию «поведения UNIQUE», дабы не порушить массу существующего T-SQL кода.

По той же ссылке вы можете обнаружить, что начиная с SQL Server 2008 существует изящное и остроумное решение проблемы множества NULL-ов в UNIQUE-колонке связанное с фильтрованными индексами, о которых мы говорили с вами в статье «Теоретические основы фильтрованных индексов в SQL Server 2008». Однако это не меняет главного вопроса: ограничение (UNIQUE) как таковое в конкретном продукте (SQL Server) реализовано либо в согласии со стандартом, либо с отклонением. До текущего момента мы имеем вторую реализацию, и никакие «workarounds» изменить этого факта не в силах.
Ограничение CHECK.
1
2
3
4
5
6
7
USE [~DB~]
GO
insert T4 VALUES (2,-3)
insert T4 VALUES (NULL,1)
insert T4 VALUES (1,NULL)
insert T4 VALUES (-2,NULL)
SELECT * FROM T4

Вновь уникальный случай! Мы уже видели когда результат сопоставления с NULL «конвертируется» из UNKNOWN в FALSE, это происходит довольно часто. Хотя очень редко, но бывает что UNKNOWN засчитывается «как есть», то есть остается самим собой. Но так, что бы UNKNOWN становился TRUE и причем делал это не в определенном, а в самом что ни на есть прямом смысле??! 8O Однако наш SQL Server поистине многолик — чего только в нем не обнаружишь покопавшись! Да, удивительно, но это ограничение считает что результат проверки UNKNOWN есть «полный OK» и без проблем вставляет строки, которые мы вовсе не ожидаем увидеть не только с точки зрения голой теории, но и так же с интуитивной точки зрения. Разумеется, такое же... неоднозначное (обозначим его так) поведение будет наблюдаться и при выполнении команды UPDATE:

1
UPDATE T4 SET Col2=NULL WHERE Col2=1

Одним словом, с CHECK-ограничениями примененным к колонкам допускающим значения NULL будьте бдительны, итоговый результат их настройки может быть диаметрально противоположен ожидаемому.

Ограничение FOREIGN KEY.
1
2
3
4
5
6
7
8
9
10
USE [~DB~]
GO
INSERT T2 VALUES (1)
GO
INSERT T3 VALUES (1)
INSERT T3 VALUES (NULL)
INSERT T3 VALUES (2)
GO
SELECT * FROM T2
SELECT * FROM T3

Это ограничение так же не обходится без «фортелей» при обращении со значением NULL. Судите сами: в родительской таблице T2 нет строки с ключом NULL. Интуитивно мы ожидаем, что в ссылающуюся на эту таблицу колонку T2ID таблицы дочерней вставить это значение не выйдет — иначе в чем ограничение-то заключается? У нас же в этом случае в таблице T3 образуются «осиротевшие» строки, без «родителей», а недопущение возникновения подобных строк и есть «мета-задача» ограничения FK. А вот не тут-то было — вставляется! И — да, образуются. И «осиротевшие», и без «родителей». А это все потому, что само ограничение устроено столь хитрым образом, что встретив в ссылающейся колонке (то есть в колонке дочерней таблицы, в нашем случае T3.T2ID) значение NULL оно полностью пропускается. То есть никакая ссылочная целостность не проверяется вообще. Отметьте себе, что если FK в дочерней таблице составной, достаточно одной колонке из состава FK-ключа в строке иметь значение NULL что бы ссылочный контроль был отменен для всей строки. И снова причинами для такого «контр-интуитивного» поведения послужили задачи и проблемы суровой действительности. Сценарий может быть таким. Приложение «персональный библиотекарь», две главные таблицы — «Полка» (родительская; означает номер полки в шкафу на которую поставлена книга) и «Книга» (дочерняя; собственно — книги). По идее, сразу после покупки книги и в момент внесения информации о ней во вторую таблицу надо бы указать (в том числе) полку, на которой она разместится. Однако вполне может быть ситуация, когда новая книга есть, а с полкой для ее хранения вы не определились (почему вы не можете определиться со столь элементарным вопросом дофантазируйте сами, ;) нам лишь важно что подобная ситуация «отложенного решения» встречается в реальной жизни на каждом шагу). Так вот невозможность/нежелание выбрать полку — не повод не заносить в каталог и саму книгу. И она вносится со значением NULL в ссылающейся колонке. Номер полки для нее вы просто исправляете позже, как «руки дойдут».

Ну а сухой остаток такой: ссылающаяся колонка дочерней таблицы может содержать любое не-NULL значение так же представленное в соответствующей колонке таблицы родительской или NULL. И вот так получается, что ограничение FK, как и ограничение CHECK, пользуется невозможной, на первый взгляд, формулой — NULL vs. <что_угодно>=TRUE! Однако, как заметил тот мудрый и многоопытный системный администратор, «это все потому, что зачастую вещи совершенно недопустимые в теории являются абсолютно необходимыми на практике». SQL Server блестяще подтверждает эту гениальную мысль.

Предложение OVER.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE [~DB~]
go
insert T1 VALUES (NULL, 'ab')
insert T1 VALUES (4, 'ef')
insert T1 VALUES (100, NULL)
insert T1 VALUES (2, 'ef')
insert T1 VALUES (NULL, 'ab')
insert T1 VALUES (1, 'ef')
insert T1 VALUES (200, NULL)
insert T1 VALUES (67, 'ab')
GO
SELECT *, SUM(Col1) OVER (PARTITION BY Col2) AS StatisticSUM FROM T1
SELECT *, COUNT(Col1) OVER (PARTITION BY Col2) AS StatisticCNT FROM T1
SELECT *, COUNT(*) OVER (PARTITION BY Col2) AS StatisticCNT FROM T1
SELECT *, ROW_NUMBER() OVER (ORDER BY Col2) AS RowN FROM T1
SELECT *, RANK() OVER (PARTITION BY Col2 ORDER BY Col1 DESC) AS RankN FROM T1

Как известно, данное предложение обеспечивает нас такой замечательной вещью как оконные функции. Они, в свою очередь, подразделяются на два класса:

  • статистические оконные функции;
  • ранжирующие оконные функции.

Приведенный чуть выше код работает с представителями как первого класса (функции SUM/COUNT), так и второго (функции ROW_NUMBER/RANK). Что же касается вопроса работы обсуждаемой конструкцией со значением NULL, то если вы внимательно изучили разделы посвященные клаузуле GROUP BY/клаузуле ORDER BY, то для вас вопрос закрыт — все то же самое. Однако, для полноты изложения, зафиксируем это полное и абсолютное совпадение в виде удобных правил:

  • аргумент PARTITION BY предложения OVER ведет себя в точности как клаузула GROUP BY. То есть, если колонка на которую указывает данный аргумент содержит значения NULL, то все строки с этим значением выделяются в отдельную группу, которую в данном случае более корректно назвать «окном». Сама же статистическая/ранжирующая функция работает с таким «NULL-окном» совершенно так же как и с любым прочим;
  • аргумент ORDER BY предложения OVER ведет себя в точности как одноименная клаузула. То есть, если колонка на которую указывает данный аргумент содержит значения NULL, то они окажутся выше любых не-NULL значений, если вы не применяете уточняющего аргумента или применяете уточняющий аргумент ASC. Если уточняющий аргумент будет DESC, то все NULL значения окажутся, соответственно, ниже любых не-NULL значений. Когда дело доходит до непосредственно ранжирующей оконной функции (как известно, аргумент ORDER BY неприменим к функциям статистическим), то порядок строк уже определен согласно только что описанным правилам и NULL значение обрабатывается такой функцией как и любое другое.

То есть, совсем одной строкой — предложение OVER рассматривает все NULL значения как идентичные между собой и меньшие любого не-NULL значения. Так же вполне естественным представляется тот факт, что статистические оконные функции остаются все теми же старыми-добрыми агрегатными функциями и ведут себя со своими NULL значениями точно так же, как это описано выше в одноименном подразделе статьи. Пример кода подчеркивает этот факт достаточно наглядно.

Заключение.

Итак, мы разобрали насколько многогранной, противоречивой, а подчас и нелогичной может быть практическая работа с этим скользким значением NULL. Автор не зря подчеркивает слово «практическая», поскольку в теории (если отбросить определенные сложности в ее первоначальном освоении) все кажется стройным и очень разумным. Однако, и в который уже раз, практика «уделывает» теорию даже не подавившись последней...

Автор постарался разобрать все значимые конструкции языка T-SQL и их работу с NULL-ом. Если читатели заметят отсутствие какого-либо элемента языка в данной статье, автор с благодарностью примет замечание о таком упущении и с удовольствием добавит описание и этого элемента. В целом, статья задумывалась как краткий, возможно — пополняемый (и с помощью читателей в том числе), практически-теоретический справочник под условным заголовком «чего следует ждать от NULL в том или ином случае». Хочется верить, что это свое предназначение статья выполнила.

Автор традиционно благодарит своих читателей за их письма и комментарии к материалам блога, ну и, конечно, ждет новых — увидимся, пока!





  • Другие части статьи:
  • 1
  • 2
  • вперед »