Порой, очевидные вещи таят в себе удивительные сюрпризы. Казалось бы, простые SQL конструкции: «IN» и «NOT IN». Что тут обсуждать-то?
Учитель философии. Конечно. Вы хотите написать ей стихи?
Г-н Журден. Нет-нет, только не стихи.
Учитель философии. Вы предпочитаете прозу?
Г-н Журден. Нет, я не хочу ни прозы, ни стихов.
Учитель философии. Так нельзя: или то, или другое.
Г-н Журден. Почему?
Учитель философии. По той причине, сударь, что мы можем излагать свои мысли не иначе как прозой или стихами.
Г-н Журден. Не иначе как прозой или стихами?
Учитель философии. Не иначе, сударь. Все, что не проза, то стихи, а что не стихи, то проза.
Мольер. Мещанин во дворянстве
Действительно! Все же просто! Все что не «IN», то «NOT IN», а что не «NOT IN», то «IN». Разве не так?
Ну что ж проведем эксперимент.
Для начала создадим таблицу HUMAN c данными о некоторых сотрудниках, включающих ID, Фамилию, и номер отдела, где они работают.
CREATE TABLE hr.human (
id_human int4 NOT NULL,
last_name text NULL,
dept int4 NULL,
CONSTRAINT human_pkey PRIMARY KEY (id_human)
);Заполним таблицу данными.
INSERT INTO hr.human (id_human, last_name, dept) VALUES
(1, 'Иванов', 1),
(2, 'Петров', 2),
(3, 'Сидоров', 3),
(4, 'Гаврилов', 1),
(5, 'Смирнов', 2),
(6, 'Андреев', 3),
(7, 'Соболев', NULL);И проверим, что содержит наша таблица.
SELECT *
FROM human
Таблица содержит сведения о 7 сотрудниках.
Tеперь выведем сведения о сорудниках работающих в отделах 1 и 2. Следующим запросом.
SELECT *
FROM human
WHERE dept IN (1,2);
Все верно. Таких сотрудников, работающих в этих отделах действительно четверо.
Ну а теперь попробуем получить сведения об остальных сотрудниках. Сделать это просто заменив в нашем примере «IN» на «NOT IN».
SELECT *
FROM human
WHERE dept NOT IN (1,2);И получим результат.
И вот тут мы видим, что один сотрудник «потерялся». Сотрудник Соболев с номером 7 не попал ни в один из списков. Не трудно заметить, что значение отдела у него не заполнено, т.е. имеет значение NULL. Именно поэтому он, очевидно, и был отбракован обоими запросами.
Почему же это произошло? Дело в том, что значение NULL следует воспринимать не как отсутствие значения, а как потенциально любое значение, или, другими словами, неизвестное значение. Увы, в теории баз данных это общепринятая концепция, не зависящая от того, как лично вы (или пользователи-предметники) интерпретируют отсутствующее значение.
Понятно, что как только «IN» наткнулся на это значение он «понял» что оно наверняка не совпадает ни с одним из перечисленных значений списка и забраковал его.
В свою очередь «NOT IN» проверял что значение наверняка не входит в перечень и так же забраковал его. И если от «IN» мы интуитивно ожидали такого поведения, то «NOT IN» повел себя не много не так ожидалось. Казалось бы “NOT” должен «переворачивать» результат, который получает следующий за ним “IN”. Но оказывается, что словосочетание «NOT IN» следует воспринимать как единую команду. интерпретировать как «значение невходит» (орфография нарушена умышленно), одним действием, а не как отрицание «значение не входит», т.е. отрицание предыдущего полученного результата.
Eсли наша гипотеза верна тогда конструкция NOT (IN…) должна включить именно инвертированных от IN список. Логика такова, что IN получит список из четырех сотрудников, а уж NOT будучи отдельным оператором его перевернет (инвертирует). Убедимся в этом.
SELECT *
FROM human
WHERE NOT (dept IN (1, 2)); 
Однако и тут мы не получили желаемого результата. Результат полностью совпал с предыдущим. Похоже в наших рассуждениях мы опять допустили логическую ошибку и все работает не так как мы себе представляли.
Дело в том, что мы, опять-таки, забыли про трехзначную логику. У логических операторов не два, а три результата, а именно: ИСТИНА, ЛОЖЬ и НЕ ЗНАЮ. Перебирая значение оператор IN натыкается на NULL и для этой строки результат выполнение этого оператора будет NULL. Соответственно оператор NOT просматривая результат полученный IN натыкается на полученное значение NULL и так же не знает, что с ним делать и не включает в результат. Таким образом строка, содержащая значение NULL, не попадает ни в один из результатов.
Это достаточно опасная ситуация для разработчика, поскольку в сложных запросах может привести к потере данных в выводимых отчетах и вообще непредсказуемое (с точки зрения неопытного разработчика) поведение. А если тестирование системы проводилось на данных, не содержащих неопределенные значения, ошибка может выявиться уже на этапе продуктива, причем не в виде сообщения об ошибке на экране монитора, а в получении неверных отчетов, на основе которых будут приниматься неверные решения.
Ну а мы не привыкли отступать! Попробуем решить проблему достаточно простым способом. Включим значение NULL в один из списков. Теперь то уж точно получиться!
Для начала пусть это будет вариант с IN.
SELECT *
FROM human
WHERE dept IN (1, 2, NULL); 
Хм? Странно. Соболев опять не попал в результат.
А что же тогда выдаст второй запрос с NOT IN?
SELECT *
FROM human
WHERE dept NOT IN (1, 2, NULL); 
Вообще ничего не выдал.
Мало того, что нам так и не удалось включить Соболева ни в один из списков, так еще и запросы перестали вести себя симметрично. Теперь мы потеряли еще и сотрудников, которые работают во вполне конкретном третьем отделе. Почему?
Что бы понять это поведение опять же надо вспомнить что такое NULL. Это не просто какое-то значение, а именно неопределенное, неизвестное значение. т.е. какое угодно.
Как же работает запрос с IN? Сравнивая поочередно конкретные значения из списка 1 и 2 с неопределенным значением отдела у Соболева, оператор не может уверенно сказать, что они совпадают, т.е. не знает совпали они или нет, а следовательно результат такого сравнения будет NULL. Но вот мы доходим до проверки третьего значения NULL. Проверяем NULL = NULL. Каков результат такого сравнения? Конечно же NULL. Ведь мы ничего не знаем о значении правой части и левой части этого сравнения. Может они совпадают, а может и нет. Результат так же не известен. Поскольку мы не нашли совпадений значения отдела Соболева ни с одним из перечисленных значений в списке IN Соболев в результат не попадает.
Ну а что же с NOT IN?
Перебирая значения NOT IN должен для всех сравнений без исключения получить ЛОЖЬ. И это удается до тех пор, пока он не добирается до значения NULL. А вдруг NULL из списка IN все же совпадет со значение отдела, например у Иванова, имеющего вполне конкретное значение 1? Мы не знаем. Соответственно Иванова в результат не включаем, как и всех остальных сотрудников. Я уже и не говорю про Соболева, ведь результат сравнения NULL<>NULL будет конечно же NULL.
Как же нам написать запрос, отражающий все данные? Например, можно явно указать дополнительное условие добавляя в список все строки со значением NULL.
SELECT *
FROM human
WHERE dept NOT IN (1, 2) OR dept IS NULL; 
Или инвертировав полученный через IN список во внешнем запросе.
select *
FROM human
WHERE id_human NOT IN (
SELECT id_human
FROM human
WHERE dept IN (1, 2)); 
Можно придумать еще множество красивых и хитроумных способов решения этой задачи, но я не знаю ни одного способа, как это сделать сочетанием классических логических операторов OR, AND, NOT и операторов IN и NOT IN.
Главное при написании запросов не забывать про значения NULL, обязательно обрабатывать эти ситуации, даже если, казалось бы, и так все работает нормально.
Будьте бдительны!
Комментарии (69)
qw1
17.08.2025 15:32Можно нормализовать NULL например в (-1)
WHERE coalesce(dept,-1) NOT IN (1,2);Не будет работать индекс на dept, если он есть, но в запросе NOT IN индекс и не должен использоваться.
topharley
17.08.2025 15:32Если индекс сделать прям вот по этой же функции coalesce(dept,-1) и искать по ней, то работать будет
shich
17.08.2025 15:32Я наткнулся на это довольно давно и больше скажу, мне попадались СУБД, где NULL==NULL. Возможно это была MSSQL
zVlad909
17.08.2025 15:32Это наверное потому что MSSQL тихой сапой допускает что NULL это бланк, пробел. Что не есть так.
Tzimie
17.08.2025 15:32Это не так. Вы путаете с Oracle
В MSSQL есть устаревший режим set ansi nulls off где можно сравнивать null на равенство
zVlad909
17.08.2025 15:32Хорошо, спасибо за уточнение.
Я не с MS SQL ни с Оракл всерьез не занимаюсь, но поддерживаю реприкацию из Оракл в MS SQL и наш DBA по этим базам мне то и дело показывает как что-то работает вот так вот тут и не так вот там. Про set ansi nulls off он тоже вспоминается говорил. Лично я считают это не допустимо. Подход к NULL должен быть един в БД, а не на выбор. Это приводит к плохим последствиям в использовании приложений.
Всерьез я работаю только с DB2 for z/OS и у меня никогда не возникало проблем и недопониманий по поводу темы статьи. Все очень четко и однозначно.
LaRN
17.08.2025 15:32Это лечится включением параметра ANSI_NULLS на MS SQL. Добавлено было очень давно, где в 2008 версии.
Akina
17.08.2025 15:32Ой, подумаешь, два NULL равны, ерунда какая. Вот когда NULL равен пустой строке - это да...
perfect_genius
17.08.2025 15:32NULL==NULL
Логично же, раз NULL - это не ноль, а непонятно что. Тогда название неправильное, да.
Oleg_Zhnyakin Автор
17.08.2025 15:32Замечание весьма верное. Но в SQL это так назвали.
Дейт же использовал более подходящий термин - UNK (unknown ), т.е. буквально неизвесное значение.
LeshaRB
17.08.2025 15:32А так чего не сделать ?
... WHERE dept not IN (1, 2) or dept is nullЗачем вложенный запрос
Astrowalk
17.08.2025 15:32— Учитель-пишет-книжки, — произнесла Фукаэри ни с того ни с сего.
Чуть подумав, Тэнго решил расценить это как вопрос. Похоже, вопросы, звучащие как утверждения, — еще одна особенность ее марсианской речи.
Харуки Мураками, "1Q84"
alexmib
17.08.2025 15:32"Для человека, не изучавшего в школе физику, мир полон чудес" ;-)
Документация:
https://www.postgresql.org/docs/17/functions-comparisons.html
9.2. Comparison Functions and Operators
Do not write
expression= NULLbecauseNULLis not “equal to”NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)https://www.postgresql.org/docs/17/functions-comparisons.html
9.25.2.
NOT INNote that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand expression yields null, the result of the
NOT INconstruct will be null, not true as one might naively expect. This is in accordance with SQL's normal rules for Boolean combinations of null values.Tip
x NOT IN yis equivalent toNOT (x IN y)in all cases. However, null values are much more likely to trip up the novice when working withNOT INthan when working withIN. It is best to express your condition positively if possible.
legolegs
17.08.2025 15:32Мне нравятся вот эти вставочки в доках по постгресу, что мол вот тут новичок получает граблями по лбу таким-то образом. Технически это избыточная инфа, но забота приятна.
zVlad909
17.08.2025 15:32Дело в том, что мы, опять-таки, забыли про трехзначную логику. У логических операторов не два, а три результата, а именно: ИСТИНА, ЛОЖЬ и НЕ ЗНАЮ.
Это интересно в каких логических операторах результат может быть "НЕ ЗНАЮ". Я таких не знаю. Это может быть в операторах сравнения если сравниваемые значения содержат NULL может получиться "НЕ ЗНАЮ", но строки с такими значениями вообще то исключаются до самих сравнений. Потому что значения NULL не сравниваемые ни с чем. Даже друг с другом.
hkm2
17.08.2025 15:32В любых. Даже очевидный
(not nulled_expression) is nullв результате дает true. Так же как иtrue_expression and nullиfalse_experssion or nullв результате дают null aka boolean unknown. И строки выборки исключаются после вычисления условий их отбора, а не до - заглядывать в будущее даже оптимизаторы SQL пока не умеют.
GidraVydra
17.08.2025 15:32Это интересно в каких логических операторах результат может быть "НЕ ЗНАЮ".Я таких не знаю.ХЗ, случайно это получилось, или это такая тонкая ирония, но это пять, адназначна.
zVlad909
17.08.2025 15:32Дело в том, что значение NULL следует воспринимать не как отсутствие значения, а как потенциально любое значение, или, другими словами, неизвестное значение. Увы, в теории баз данных это общепринятая концепция, не зависящая от того, как лично вы (или пользователи-предметники) интерпретируют отсутствующее значение.
Если бы это (выделеное жирным) было бы так то тогда 2 = NULL давало бы YES. И 2 < NULL тоже и вообще все старнения с NULL были бы положительными, но это не так.
"Неизвестное" и "любое" это не одно и тоже.
Кстати по этому существуют два специальных оператора сравнения: IS NULL, and IS NOT NULL. Во всех остальных предикатах строки содержащие NULL в колонках участвующих в этих предикатах просто отбрасываются.
Вот как это объясненно в доках DB2 for z/OS (на мой взгляд наиболее полное и корректное описание. Возможно не совпадающее с некоторыми дркгими РСУБД, например MS SQL):
В DB2 для z/OS концепции IN, NOT IN и NULL являются основополагающими для построения SQL-запросов и обработки данных. Значения NULL:
NULL представляет неизвестное или отсутствующее значение в столбце. Оно не эквивалентно нулю, пустой строке или любому другому определённому значению.
Столбцы могут быть определены так, чтобы разрешать или запрещать значения NULL. Если столбец определён как NOT NULL, он не может содержать значения NULL. Столбцы первичного ключа всегда должны быть определены как NOT NULL.
-
Значения NULL требуют особой обработки при сравнении. Стандартные операторы сравнения (=, <, >) не работают с NULL. Вместо этого для проверки наличия или отсутствия значения NULL используются предикаты IS NULL и IS NOT NULL.
Предикат IN:
Предикат IN используется для проверки соответствия значения выражения любому значению в списке значений или результирующем наборе подзапроса. Синтаксис: выражение IN (значение1, значение2, ...) или выражение
IN (подзапрос). Пример:
SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE JOB IN ('CLERK', 'ANALYST');Предикат NOT IN:
Предикат NOT IN используется для проверки того, не совпадает ли значение выражения ни с одним значением в списке значений или результирующем наборе подзапроса. Синтаксис: выражение NOT IN (значение1, значение2, ...) или выражение NOT IN (подзапрос)
Важное примечание с NULL:
Если список значений или результирующий набор подзапроса для NOT IN содержит значение NULL, предикат NOT IN всегда будет иметь значение unknown (false), даже если значение выражения явно не равно ни одному из ненулевых значений. Это связано с тем, что NULL нельзя сравнить ни с одним значением с помощью стандартных операторов сравнения, и, следовательно, его отсутствие в списке не может быть однозначно подтверждено.
Пример (демонстрирующий проблему с NULL):
SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE JOB NOT IN ('CLERK', 'ANALYST', NULL);Скорее всего, это не вернет ни одной строки, если JOB может быть NULL. Чтобы корректно исключить значения NULL или обработать их явно, IS NOT NULL следует использовать вместе с NOT IN, или подзапрос должен отфильтровывать значения NULL.
Обработка значений NULL с помощью IN/NOT IN: При использовании IN или NOT IN со столбцами, которые могут содержать значения NULL, рассмотрите возможность фильтрации значений NULL в подзапросе или использования IS NOT NULL в предложении WHERE, чтобы избежать непредвиденных результатов, особенно с NOT IN. Исправленный пример для NOT IN с NULL:
SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE JOB NOT IN ('CLERK', 'ANALYST') AND JOB IS NOT NULL;
urvanov
17.08.2025 15:32Там у NULL все операции работают по-особенному. Именно поэтому и нужно всегда IS NULL проверять его, а не другим способом. Как тут уже выше писали, мир полон физики, если не знаешь магию. В статье хорошее напоминание об особенностях NULL, я считаю. Повторить пойдёт.
garregusev
17.08.2025 15:32спасибо за статью! это ровно то чего всегда ожидаю от хабра - чтобы интересно и полезно, а ещё и с классной подачей написано!
такие статьи стали редкостью в потоке успешного успеха, корпоративной нативной рекламы и вайбкодинга :-)
LaRN
17.08.2025 15:32Те выходит что оператор not in (null, 1, 2) по идее всегда будет пустой датасет отдавать, потому что первое сранение всегда дает null, а дальше включается лен вая логика расчета логических выражений и проверок с 1 и 2 уже не будет?
Timofeuz
17.08.2025 15:32Более простое объяснение это то, во что разворачивается IN и NOT IN:
IN: val = 1 OR val = 2 OR val = NULL;
NOT IN: val != 1 AND val != 2 AND val != NULL;
Во втором случае falsy значение (сравнение с NULL) в сочетании с AND делает всё false.
SystemX
17.08.2025 15:32А что же тогда выдаст второй запрос с NOT IN?
Поправьте запрос после данного текста. Скопировали, но не отредактировали.
NeoCode
17.08.2025 15:32Интересно было бы сравнить логику NULL в БД, нуллабельность и опционалы в языках программирования, а также поведение NaN (not a number) в числах с плавающей точкой. Что там общего, в чем различия и как лучше всего?
maslyaev
17.08.2025 15:32Сложности с NULL-ом в SQL это следствие ошибки проектирования стандарта. SQL просто очень древний, и тогда ещё не разобрались, что значение "нет значения" это не какая-то нештатная ситуация, а на редкость полезная фича. И это совсем не обязательно означает "значение неизвестно". Может быть очень даже известно, что его нет. Или значение известно, но его получение не прошло RLS. Или что угодно. В реляционной алгебре NULL-ы сплошь и рядом вылазят на outer join-ах даже если база идеально нормализована и все поля non-nullable.
Конечно же, NULL всегда должен быть равен NULL. Тернарная логика здесь не нужна никогда никому и ни для чего. За 30 лет работы с базами данных я набил очень много шишек об этот несчастный NULL, но ни разу этот наворот с NULL-ом не оказался полезен. Он всегда только мешает.
В других языках программирования (например Питон, JS, Rust) пустое значение равно пустому значению, и никто не жаловался и не просил срочно заменить на этот изврат с тернарной логикой.
Akina
17.08.2025 15:32И это совсем не обязательно означает "значение неизвестно". Может быть очень даже известно, что его нет.
Боюсь, что все ваши шишки за 30 лет - именно вот от этого дополнения про "известно, что его нет".
NULL - это именно "неизвестно", это маппирование UNKNOWN на синтаксис SQL.
А вот "значения нет" - это само по себе вполне определённое и известное значение, входящее в список возможных значений атрибута, со своим уникальным идентификатором, которое ничего общего с NULL не имеет. Часто это значение "не задано" или типа того, и часто с отдельной кодовой веткой его обработки.
Это вы уже в результате, полученном при выполнении запроса, можете интерпретировать NULL как "мне известно, что значения нет".
В других языках программирования (например Питон, JS, Rust) пустое значение равно пустому значению, и никто не жаловался и не просил срочно заменить на этот изврат с тернарной логикой.
Как правило, у вас найдётся возможность скорректировать поведение СУБД в нужную вам сторону. ANSI NULLS в SQL Server, null-safe compare operator <=> в MySQL и т.п.
А вот скорректировать наоборот, чтобы все NULL различались, когда они равны - это сложнее.
qw1
17.08.2025 15:32Как правило, у вас найдётся возможность скорректировать поведение СУБД в нужную вам сторону. ANSI NULLS в SQL Server
Это безумие, писать на каком-то своём диалекте, когда весь мир уже определился, что хочет ANSI SQL, пусть неудобный и кривой, зато везде одинаковый. Да и на проекте с историей сделать ALTER DATABASE слишком рискованно, вероятно что-то да сломается.
null-safe compare operator <=> в MySQL
Который не помогает например в ситуации из статьи -
dept NOT IN (2,3)и не поддерживается ORM.А вот скорректировать наоборот, чтобы все NULL различались, когда они равны - это сложнее.
Не надо такого. Не могу представить ситуацию, когда в
SELECT DISTINCT deptили
SELECT ... GROUP BY deptнужно будет получить столько разных групп c пустым dept, сколько вообще записей с dept=NULL.
maslyaev
17.08.2025 15:32NULL - это именно "неизвестно", это маппирование UNKNOWN на синтаксис SQL
NULL-ы, возвращаемые внешними соединениями и коррелированными подзапросами, имеют семантику именно "нет значения". Семантика "неизвестно" применима только для nullable полей, по которым договорились, что разрешается полениться их заполнять. Не то что бы совсем редкая ситуация, но всё же не основной мотив для nullability.
В примере в статье human.dept is null у господина Соболева скорее всего не потому что он неизвестно в каком департаменте, а потому что он пока ещё ни в каком департаменте. И это известно.
А вот "значения нет" - это само по себе вполне определённое и известное значение, входящее в список возможных значений атрибута, со своим уникальным идентификатором, которое ничего общего с NULL не имеет.
Магические константы для обозначения отсутствующих значений разных типов? Шутите?
ANSI NULLS
Здесь уже написали, что это безумие. От себя добавлю, что меня не радует перспектива разъяснять эту особенность каждому новому участнику проекта под роспись.
Тернарная логика NULL-ов это одна из тех вещей, про которые говорят "ошибка на миллиард долларов". С учётом древности истории, распространённости технологии и объёма вызываемого этим решением геморроя, думаю, совокупных убытков уже сильно больше, чем на миллиард. Во всём остальном SQL чудо как хорош, и похоже на то, что он теперь с нами навсегда. Ошибку на миллиард так просто не исправить, и поэтому единственное, что нам реально остаётся, это страдать и добавлять свои копеечки к совокупным убыткам.
edo1h
17.08.2025 15:32Как правило, у вас найдётся возможность скорректировать поведение СУБД в нужную вам сторону
не такое уж и правило. постгрес входит в топ-3, в нём сходу вспоминаются только
coalesce/is null.
edo1h
17.08.2025 15:32скорее проблема от смешения «нет значения» и «значение неизвестно». задумывался null для второго, а используется чаще для первого
Alexx5222
17.08.2025 15:32Повеселила фраза "орфография нарушена умышленно" при огромном количестве грамматических и синтаксических ошибок.
ilnurr
17.08.2025 15:32Лучше всегда использовать явное IS NOT TRUE вместо NOT.
WHERE dept IN (1, 2) IS NOT TRUE - включает FALSE и NULL.
Если NULL не хочется включать, тогда IS FALSE:
WHERE dept IN (1, 2) IS FALSE - включает только FALSE без NULL
Politura
Конкретно в этом случае, красивый и хитроумный способ это сделать поле department not null, чтоб нельзя было завести сотрудника вне какого-либо отдела. Да и в целом, необходимость null значений бывает довольно редко и каждый раз означает денормализацию данных. Так-то без денормализации далеко не всегда можно обойтись, но в общем это неклевая штука в реляционных базах данных, нужно четко понимать зачем именно ее добавляешь.
zVlad909
Хочется задать вопросы.
1. Каким образом использоваение NULL связано с нормализацией (равно как и с денормализацией)?
2. Интересный ход мысли отталкивающийся от странной предпосылки что денормализация это нечто желаемое. И не очень понятно что, нормализацию или денормализацию, надо понимать зачем добавлять. Поясните свою мысль, пожалуцйста.
Politura
В полностью нормализованной базе null-ов просто не будет. В примере из статьи, вместо того, чтоб добавлять departmentId null поле, нормализацией будут две таблицы, типа:
Но, такой вариант приведет к потере производительности когда нам надо работать с сотрудниками, а не с персонами, из-за дополнительного джойнта в селекте.
Жаль, я вовсе не хотел чтоб сложилось впечатление, что денормализация это нечто желаемое. И даже изначально написал, что денормализация это "неклевая штука". Наверное слишком я старый и слова "клевый" и "неклевый" уже непонятные стали, тогда поясню понятными словами: На мой взгляд, надо в первую очередь стремиться к нормализации. Но очень часто в целях повышения производительности приходится делать денормализацию в некоторых местах.
zVlad909
Это, интересно, согласно какой нормальной форме (1, 2, 3, 4....) null-ов быть не должно?
Пример это про целостность ссылок (referential integrity), а не про нормализацию.
Одной из главных особенностей реляционной модели всегда считалась независимость логической структуры от физической, а производительность в первую очередь зависит от физической структуры.
Да можно денормализируя структуру сократить количество соединений таблиц. Но если грамотно построить ключи (не опираться только на identity type), то можно и сохранить нормальности и иметь хорошую производительность. Например можно вместо personal_id int сделать ключем более содержательный идентификатор, который будет доставлять больше информации которая в свою очередь будучи внешним ключом в другой таблице позволит обойтись без соединения по крайней мере в большинстве случаев. Но случай с физическими лицами не самый хороший пример для этого потому что для уникальности надо слишком много информации закладывать в такой ключ. Но по современным подходам вообще во всех таблицах первичный ключ это ни о чем не говорящее целое число. В результате получается много соединений даже для самых простых запросов.
В остальном я полностью с Вами согласен. Начинать надо с нормализации, но именно той что имеет нормальную форму 1, 2, 3, а не как у Вас в примере.
Я тоже не молод, очень даже не.
edo1h
из статьи на хабре же (ибо я не chatgpt помнить наизусть эти номера):
Шестая нормальная форма
Переменная отношения находится в шестой нормальной форме тогда и только тогда, когда она удовлетворяет всем нетривиальным зависимостям соединения. Из определения следует, что переменная находится в 6НФ тогда и только тогда, когда она неприводима, то есть не может быть подвергнута дальнейшей декомпозиции без потерь
то есть при максимальной нормализации у нас поле dept будет в отдельной таблице, и необходимость в null отпадёт
Tsimur_S
У вас есть поле dept которое ссылается на таблицу depts, где у вас есть поле dept_name, которое допускает NULL.
Это противоречит 6НФ либо любой другой нормальной форме? Если да то какой и как?
Если нет то как связаны НФ и NULL?
edo1h
грубо можно сформулировать так: в таблице только одно поле кроме pk. при такой нормализации необходимость в NULL для «нет значения» отпадает, мы просто не добавляем строчку.
заметьте, что для NULL в смысле «значение неизвестно» поведение как раз корректно, мы не можем про эту строчку скачать ни что она
IN (SELECT …), ни что онаNOT IN (SELECT …)zVlad909
Работаю с реляционными БД (настоящими РБД) с начала 90-х. Да, знаю что нормальных форм может быть сколько угодно, но никогда не видел определений более чем четвертой НФ. А на практике достаточно трех (ни разу не сталкивался с хотя бы НФ 2, а зачастую и НФ 1 не удоблетворяло, с этим вообще полный бардак на самом деле, и не только с этим. Такое впечатление что для большинства разработчиков (сужу по западным, но думаю что в России не лучше) теория это одно, а "мы" это совсем другое).
Я конечно погуглю про шестую НФ, но есть есть ссылка, то будет тоже здорово.
edo1h
да на практике нф выше 3 нужны только для расширения кругозора )
RSM
Стремление к достижению нормальной формы выше 3й в большинстве случаев ненормально :)
Oleg_Zhnyakin Автор
Не согласен с вами. Каждая нормальная форма предполагает сокращение избыточности и как следствие исключение возникающих аномалий. Отношения, находящиеся не в 4 и не в 5 нормальной форме и при этом находящиеся во всех предыдущих редкое явление, но от этого он не становиться менее неприятным чем скажем отношение не в третьей нормальной форме.
SergeyProkhorenko
Шестая нормальная форма (под брендом Anchor Modeling) нужна для темпоральных баз данных, которые используются в хранилищах данных. В России их используют Авито, Яндекс Такси и ВТБ. Лучше всего они себя проявляют там, где структура данных очень изменчива. Это экономит очень много денег на поддержке хранилища данных. С инструментарием для создания БД в шестой нормальной форме пока довольно плохо. На SQL это делать очень трудоемко, и поэтому на практике используют Python, обрабатывающий таблицы метаданных на Excel. Вот попытка сделать DSL для шестой нормальной формы
zVlad909
Уважаемый Сергей, речь идет о реляционных базах данных. В реляционных БД структура может изменяться, но это не есть "изменчивость" структуры.
SQL это не база данных, это язык манипулирования данными в реляционных базах данных. Нормализация имеет отношение к собственно данным, а не к языку. Хотя именно для того чтобы язык SQL мог эффективно использоваться и требуется нормализация, но это не причина, а следствие нормализации.
SergeyProkhorenko
Ищите по словам Anchor Modeling. Это и есть шестая нормальная форма в чистом виде
zVlad909
Минуточку (wait a second). Насколько я понмню, а помню я хорошо, это первая НФ требует чтобы на пересечении строки и столбца было атомарное значение, т.е. не допускающее декомпозицию.
Ну и причем эдесь NULL? Это атомарное значение не допускающее (не требующее) декомпозиции.
Где Вы нашли такие определения? Я реально в шоке.
edo1h
https://habr.com/ru/articles/937964/comments/#comment_28723274
https://habr.com/ru/articles/937964/comments/#comment_28722828
набрал в гугле «нормальные формы», это очевидно же )
https://habr.com/ru/articles/254773/
zVlad909
Смотри ка, и в правду гугл дает сразу ссыслку на статью в Хабр.
Посмотрел статью. Весьма оригинальное видение в целом на реляционные базы данные, не классическое я бы сказал. Судя по всему (не уверен) перевод документа по MySQL. А документ с притензией на оригинальность.
Может оставлю комментарий в ту статью, не знаю. Но вопрос причем здесь NULL обращенный к Вам остается открытым.
edo1h
да ничего оригинального вроде, но я не вчитывался, только пример 6нф посмотрел
в англоязычной вики пример понагляднее имхо:
https://en.wikipedia.org/wiki/Sixth_normal_form#Examples
ох, ну я вроде расписал в соседних комментариях, ссылки на них дал.
сейчас попробую ещё раз, но сомневаюсь, что получится другими словами, слишком мало времени прошло ))))
итак, sql проектировали высоколобые надмозги, и они трактовали null как «значение неизвестно». отдел есть, но мы не знаем какой. соответственно, мы не можем сказать что работник, у которого тут стоит null в коде отдела, работает в каком-либо отделе, или не работает в каком-то отделе. и
IN, иNOT INэтого работника не включают. ответ как в анекдоте про математиков — абсолютно точный и абсолютно бесполезный.на практике же null обычно трактуется как «значения нет». оно не неизвестно, оно известно, но пустое. и такое его использование совместно с тернарной логикой, доставшейся нам от тех высоколобых, вызывает стрельбу по ногам короткими очередями.
так вот, экстремальная нормализация позволяет нам отказаться от null вообще. заводится отдельная таблица, в которой указывается принадлежность сотрудника отделу. два поля — pk и код отдела.
в этом случае null нам уже не нужен. сотрудник не принадлежит никакому отделу — так не добавляем строчку, да и всё.
не то, чтобы в этом случае нельзя было выстрелить в ноги, но именно проблемы неочевидности трактовки null'а можно избежать.
zVlad909
Имена "в студию".
edo1h
сходу не назову тех, кто заседал в комитетах в то время. это так важно сегодня?
и, если вы не поняли, про «высоколобых» было написано не совсем серьёзно. разумеется, я очень уважаю создателей sql, то, что язык живёт и активно используется до сих пор, лучшее подтверждение тому, что он вышел очень удачным.
но и Кодд, и Дейт критиковали NULL в том виде, в котором он попал в стандарт:
https://en.wikipedia.org/wiki/Null_(SQL)#Criticisms
заметьте, это именно те люди, кто «на самом деле стоял у истоков реляционной технологии БД», и к авторитету которых вы апеллируете
zVlad909
Язык и собственно база данных это две разные вещи. Язык SQL может быть применим, и на самом деле применяется, к вовсе не реляционным БД. Кодд не отрицал NULL как таковой (согласно Вашей ссылке), а предлагал более сложное этого трактование (спасибо за ссылку, я про это не знал).
В книге Дейта от 1984 года мы находим такое мнение:
Да это про DB2, но не видно что Дейт в 1984 году отрицает Null как написано по вашей ссылке:
Это было заявленно в 2007 году в неком "Третьем манифесте". Я его еще не читал. Но кмк речь шла не о собственно Null, а о нечеткости его интерпретации в функциях SQL, но надеюсь не в самой реляционной модели. Посмотрим.
Лично я не вижу никаких проблем с Null, использую их там где так может быть что атрибут сущности не может быть определен, или пока не может быть определен, и в тех случаях когда в одной таблице объединены две или более сущностей с многими одинаковыми атрибутами и немногими разными. По уму их надо бы представлять разными отношениями, но если есть случаи когда их разница не играет роли то их объединение упрощает программирование.
Было и такое мнение (согласно опять же Вашей ссылке):
А может быть как раз и на оборот, модель задает правила для функций и они должны реализоваться во всех функциях одинаково. Или в каждой функции должно быть особое описание для заначений Null если трактовки модели оказалось недостаточно. Такое тоже может быть.
zVlad909
Собственно тех кого я имел в виду Вы назвали.
Я понял наши разхождения. Для меня есть реляционная модель и есть SQL - язык. Это две разные вещи. Причем модель первична, а язык вторичен.
По факту же частно все оказывается перевернуто с ног на голову. Разработчики SQL начинают уродовать модель в угоду порочным желаниям разработчиков приложений для реляционной модели. Это факт медицинский, я про это говорю уже давно.
И в итоге в головах многих современных разработчиков приложений образуется настоящая каша. Статья и некоторые комментарии к ней есть пример этой каши.
zVlad909
Статья в Вики в Вашей ссылке начинается вот с этого (еще раз спасибо за ссылку):
Из чего следует что NULL это элемент реляционной модели, но не SQL, языка. А вот проблема NULL это проблема SQL.
Т.е. в итоге ни Кодд ни Дейт ничего не критиковали и критиковать не могли. Ничего кроме некоторых интерпретаций (трактовок) NULL в SQL.
Для меня есть некоторое подмножество SQL, необходимое и достаточное для всего что нужно чтобы манипулировать данными на уровне базы данных, реляционной или нет, не важно. SQL и к нереляционным БД можно пристегнуть, я знаю пример.
После того как с манипуляцией данными в БД процесс заканчивается и остаются вопросы в дело должен вступать язык другого уровня, уровня логики не покрываемой реляционной моделью данных. Таких языков много, начиная с Кобол, и заканчивая я даже не знаю чем.
Тенденция же такова что в SQL кто-то решил запихать всё. Впихнуть невпихуемое. И тут начались проблемы.
zVlad909
Это, извините, не о чем. Это Вам к "высоколобым надмозгом". С моей стороны no comments. Смотрим дальше.
Я привел ниже как трактуется NULL в руководстве DB2 for z/OS. К этой трактовке вопросы есть?
Это конечно оригинальный подход. Нет слов. Но и с IS NULL, and IS NOT NULL эта проблема тоже прекрасно решается.
Что касаемо "экстремальной" нормализации, то я скоро напишу статью про классическую нормализацию от тех кто на самом деле стоял у истоков реляционной технологии БД, а не от "высоколобых....", а более того если Вы имеете в виду пресловутую НФ 6, то даже там про это ничего нет.
Нет никакой проблемы неочевидности трактовки null. Ну может только в "высоколобых надмозгах". В нормальных мозгах все с null очевидно. И нет никакой связи null с нормализацией.
edo1h
разумеется. там же просто пересказан стандарт.
вот рядом примерно то, что я написал, но другими словами:
https://habr.com/ru/articles/937964/comments/#comment_28723754
zVlad909
Нет это не пересказ стандарта, это то как трактуется (так и только так) NULL в базе данных, которая была создана на принципах сформулированных теми кто "изобрел" реляционную модеоь и парадигму и на которой и создавался этот стандарт.
Это конечно не мешает другим БД допускать другие трактовки (типа NULL===NULL), но это уже проблемы других БД и тех кто ими пользуется.