Comments 67
Можно придумать еще множество красивых и хитроумных способов решения этой задачи
Конкретно в этом случае, красивый и хитроумный способ это сделать поле department not null, чтоб нельзя было завести сотрудника вне какого-либо отдела. Да и в целом, необходимость null значений бывает довольно редко и каждый раз означает денормализацию данных. Так-то без денормализации далеко не всегда можно обойтись, но в общем это неклевая штука в реляционных базах данных, нужно четко понимать зачем именно ее добавляешь.
Да и в целом, необходимость null значений бывает довольно редко и каждый раз означает денормализацию данных. Так-то без денормализации далеко не всегда можно обойтись, но в общем это неклевая штука в реляционных базах данных, нужно четко понимать зачем именно ее добавляешь.
Хочется задать вопросы.
1. Каким образом использоваение NULL связано с нормализацией (равно как и с денормализацией)?
2. Интересный ход мысли отталкивающийся от странной предпосылки что денормализация это нечто желаемое. И не очень понятно что, нормализацию или денормализацию, надо понимать зачем добавлять. Поясните свою мысль, пожалуцйста.
В полностью нормализованной базе null-ов просто не будет. В примере из статьи, вместо того, чтоб добавлять departmentId null поле, нормализацией будут две таблицы, типа:
create table persons (
personId int not null primary key,
firstName varchar(100) not null,
lastName varchar(100) not null
)
create table employers (
personId int not null primary key,
departmentId int not null,
constraint fk_employer_person foreign key (personId) references persons (personId),
constraint fk_employer_department foreign key (departmentId) references departments (departmentId)
)
Но, такой вариант приведет к потере производительности когда нам надо работать с сотрудниками, а не с персонами, из-за дополнительного джойнта в селекте.
Интересный ход мысли отталкивающийся от странной предпосылки что денормализация это нечто желаемое
Жаль, я вовсе не хотел чтоб сложилось впечатление, что денормализация это нечто желаемое. И даже изначально написал, что денормализация это "неклевая штука". Наверное слишком я старый и слова "клевый" и "неклевый" уже непонятные стали, тогда поясню понятными словами: На мой взгляд, надо в первую очередь стремиться к нормализации. Но очень часто в целях повышения производительности приходится делать денормализацию в некоторых местах.
Это, интересно, согласно какой нормальной форме (1, 2, 3, 4....) null-ов быть не должно?
Пример это про целостность ссылок (referential integrity), а не про нормализацию.
Одной из главных особенностей реляционной модели всегда считалась независимость логической структуры от физической, а производительность в первую очередь зависит от физической структуры.
Да можно денормализируя структуру сократить количество соединений таблиц. Но если грамотно построить ключи (не опираться только на identity type), то можно и сохранить нормальности и иметь хорошую производительность. Например можно вместо personal_id int сделать ключем более содержательный идентификатор, который будет доставлять больше информации которая в свою очередь будучи внешним ключом в другой таблице позволит обойтись без соединения по крайней мере в большинстве случаев. Но случай с физическими лицами не самый хороший пример для этого потому что для уникальности надо слишком много информации закладывать в такой ключ. Но по современным подходам вообще во всех таблицах первичный ключ это ни о чем не говорящее целое число. В результате получается много соединений даже для самых простых запросов.
В остальном я полностью с Вами согласен. Начинать надо с нормализации, но именно той что имеет нормальную форму 1, 2, 3, а не как у Вас в примере.
Я тоже не молод, очень даже не.
из статьи на хабре же (ибо я не chatgpt помнить наизусть эти номера):
Шестая нормальная форма
Переменная отношения находится в шестой нормальной форме тогда и только тогда, когда она удовлетворяет всем нетривиальным зависимостям соединения. Из определения следует, что переменная находится в 6НФ тогда и только тогда, когда она неприводима, то есть не может быть подвергнута дальнейшей декомпозиции без потерь
то есть при максимальной нормализации у нас поле dept будет в отдельной таблице, и необходимость в null отпадёт
У вас есть поле dept которое ссылается на таблицу depts, где у вас есть поле dept_name, которое допускает NULL.
Это противоречит 6НФ либо любой другой нормальной форме? Если да то какой и как?
Если нет то как связаны НФ и NULL?
грубо можно сформулировать так: в таблице только одно поле кроме pk. при такой нормализации необходимость в NULL для «нет значения» отпадает, мы просто не добавляем строчку.
заметьте, что для NULL в смысле «значение неизвестно» поведение как раз корректно, мы не можем про эту строчку скачать ни что она IN (SELECT …), ни что она NOT IN (SELECT …)
Работаю с реляционными БД (настоящими РБД) с начала 90-х. Да, знаю что нормальных форм может быть сколько угодно, но никогда не видел определений более чем четвертой НФ. А на практике достаточно трех (ни разу не сталкивался с хотя бы НФ 2, а зачастую и НФ 1 не удоблетворяло, с этим вообще полный бардак на самом деле, и не только с этим. Такое впечатление что для большинства разработчиков (сужу по западным, но думаю что в России не лучше) теория это одно, а "мы" это совсем другое).
Я конечно погуглю про шестую НФ, но есть есть ссылка, то будет тоже здорово.
да на практике нф выше 3 нужны только для расширения кругозора )
Стремление к достижению нормальной формы выше 3й в большинстве случаев ненормально :)
Не согласен с вами. Каждая нормальная форма предполагает сокращение избыточности и как следствие исключение возникающих аномалий. Отношения, находящиеся не в 4 и не в 5 нормальной форме и при этом находящиеся во всех предыдущих редкое явление, но от этого он не становиться менее неприятным чем скажем отношение не в третьей нормальной форме.
Шестая нормальная форма (под брендом Anchor Modeling) нужна для темпоральных баз данных, которые используются в хранилищах данных. В России их используют Авито, Яндекс Такси и ВТБ. Лучше всего они себя проявляют там, где структура данных очень изменчива. Это экономит очень много денег на поддержке хранилища данных. С инструментарием для создания БД в шестой нормальной форме пока довольно плохо. На SQL это делать очень трудоемко, и поэтому на практике используют Python, обрабатывающий таблицы метаданных на Excel. Вот попытка сделать DSL для шестой нормальной формы
Уважаемый Сергей, речь идет о реляционных базах данных. В реляционных БД структура может изменяться, но это не есть "изменчивость" структуры.
SQL это не база данных, это язык манипулирования данными в реляционных базах данных. Нормализация имеет отношение к собственно данным, а не к языку. Хотя именно для того чтобы язык SQL мог эффективно использоваться и требуется нормализация, но это не причина, а следствие нормализации.
Ищите по словам Anchor Modeling. Это и есть шестая нормальная форма в чистом виде
Из определения следует, что переменная находится в 6НФ тогда и только тогда, когда она неприводима, то есть не может быть подвергнута дальнейшей декомпозиции без потерь
Минуточку (wait a second). Насколько я понмню, а помню я хорошо, это первая НФ требует чтобы на пересечении строки и столбца было атомарное значение, т.е. не допускающее декомпозицию.
Ну и причем эдесь NULL? Это атомарное значение не допускающее (не требующее) декомпозиции.
Где Вы нашли такие определения? Я реально в шоке.
https://habr.com/ru/articles/937964/comments/#comment_28723274
https://habr.com/ru/articles/937964/comments/#comment_28722828
Где Вы нашли такие определения?
набрал в гугле «нормальные формы», это очевидно же )
https://habr.com/ru/articles/254773/
набрал в гугле «нормальные формы», это очевидно же )https://habr.com/ru/articles/254773/
Смотри ка, и в правду гугл дает сразу ссыслку на статью в Хабр.
Посмотрел статью. Весьма оригинальное видение в целом на реляционные базы данные, не классическое я бы сказал. Судя по всему (не уверен) перевод документа по MySQL. А документ с притензией на оригинальность.
Может оставлю комментарий в ту статью, не знаю. Но вопрос причем здесь NULL обращенный к Вам остается открытым.
Посмотрел статью. Весьма оригинальное видение в целом на реляционные базы данные, не классическое я бы сказал
да ничего оригинального вроде, но я не вчитывался, только пример 6нф посмотрел
в англоязычной вики пример понагляднее имхо:
https://en.wikipedia.org/wiki/Sixth_normal_form#Examples
Но вопрос причем здесь NULL обращенный к Вам остается открытым.
ох, ну я вроде расписал в соседних комментариях, ссылки на них дал.
сейчас попробую ещё раз, но сомневаюсь, что получится другими словами, слишком мало времени прошло ))))
итак, sql проектировали высоколобые надмозги, и они трактовали null как «значение неизвестно». отдел есть, но мы не знаем какой. соответственно, мы не можем сказать что работник, у которого тут стоит null в коде отдела, работает в каком-либо отделе, или не работает в каком-то отделе. и IN, и NOT IN этого работника не включают. ответ как в анекдоте про математиков — абсолютно точный и абсолютно бесполезный.
на практике же null обычно трактуется как «значения нет». оно не неизвестно, оно известно, но пустое. и такое его использование совместно с тернарной логикой, доставшейся нам от тех высоколобых, вызывает стрельбу по ногам короткими очередями.
так вот, экстремальная нормализация позволяет нам отказаться от null вообще. заводится отдельная таблица, в которой указывается принадлежность сотрудника отделу. два поля — pk и код отдела.
в этом случае null нам уже не нужен. сотрудник не принадлежит никакому отделу — так не добавляем строчку, да и всё.
не то, чтобы в этом случае нельзя было выстрелить в ноги, но именно проблемы неочевидности трактовки null'а можно избежать.
итак, sql проектировали высоколобые надмозги, и они трактовали null как «значение неизвестно».
Имена "в студию".
сходу не назову тех, кто заседал в комитетах в то время. это так важно сегодня?
и, если вы не поняли, про «высоколобых» было написано не совсем серьёзно. разумеется, я очень уважаю создателей sql, то, что язык живёт и активно используется до сих пор, лучшее подтверждение тому, что он вышел очень удачным.
но и Кодд, и Дейт критиковали NULL в том виде, в котором он попал в стандарт:
https://en.wikipedia.org/wiki/Null_(SQL)#Criticisms
заметьте, это именно те люди, кто «на самом деле стоял у истоков реляционной технологии БД», и к авторитету которых вы апеллируете
Язык и собственно база данных это две разные вещи. Язык SQL может быть применим, и на самом деле применяется, к вовсе не реляционным БД. Кодд не отрицал NULL как таковой (согласно Вашей ссылке), а предлагал более сложное этого трактование (спасибо за ссылку, я про это не знал).
В книге Дейта от 1984 года мы находим такое мнение:
Система DB2 поддерживает концепцию неопределенного значения данных. Фактически любой столбец может содержать неопределенное значение, если в определении этого столбца в предложении CREATE TABLE явным образом не специфицировано NOT NULL (неопределенное значение не допускается). Неопределенное значение— это специальное значение, которое используется для того, чтобы представлять «неизвестное значение» или «неприменимое значение». Это не то же самое, что пробел или ноль. Например, запись поставки может содержать неопределенное значение поля КОЛИЧЕСТВО (известно, что поставка имела место, но неизвестен объем поставки). Запись поставщика может также содержать неопределенное значение в столбце СОСТОЯНИЕ (может быть, например, что СОСТОЯНИЕ не соотносится по некоторой причине поставщикам в Сан-Хосе).
Да это про DB2, но не видно что Дейт в 1984 году отрицает Null как написано по вашей ссылке:
...authors of The Third Manifesto, have suggested that the SQL Null implementation is inherently flawed and should be eliminated altogether,[34] pointing to inconsistencies and flaws in the implementation of SQL Null-handling (particularly in aggregate functions) as proof that the entire concept of Null is flawed and should be removed from the relational model.
...предположили, что реализация SQL Null изначально ошибочна и должна быть полностью исключена[34], указывая на несоответствия и недостатки в реализации обработки SQL Null (особенно в агрегатных функциях) как на доказательство того, что вся концепция Null ошибочна и должна быть удалена из реляционной модели.
Это было заявленно в 2007 году в неком "Третьем манифесте". Я его еще не читал. Но кмк речь шла не о собственно Null, а о нечеткости его интерпретации в функциях SQL, но надеюсь не в самой реляционной модели. Посмотрим.
Лично я не вижу никаких проблем с Null, использую их там где так может быть что атрибут сущности не может быть определен, или пока не может быть определен, и в тех случаях когда в одной таблице объединены две или более сущностей с многими одинаковыми атрибутами и немногими разными. По уму их надо бы представлять разными отношениями, но если есть случаи когда их разница не играет роли то их объединение упрощает программирование.
Было и такое мнение (согласно опять же Вашей ссылке):
author Fabian Pascal, have stated a belief that "how the function calculation should treat missing values is not governed by the relational model."
автор Фабиан Паскаль высказал убеждение, что «то, как вычисление функции должно обрабатывать пропущенные значения, не регулируется реляционной моделью»
А может быть как раз и на оборот, модель задает правила для функций и они должны реализоваться во всех функциях одинаково. Или в каждой функции должно быть особое описание для заначений Null если трактовки модели оказалось недостаточно. Такое тоже может быть.
сходу не назову тех, кто заседал в комитетах в то время. это так важно сегодня?
Собственно тех кого я имел в виду Вы назвали.
Я понял наши разхождения. Для меня есть реляционная модель и есть SQL - язык. Это две разные вещи. Причем модель первична, а язык вторичен.
По факту же частно все оказывается перевернуто с ног на голову. Разработчики SQL начинают уродовать модель в угоду порочным желаниям разработчиков приложений для реляционной модели. Это факт медицинский, я про это говорю уже давно.
И в итоге в головах многих современных разработчиков приложений образуется настоящая каша. Статья и некоторые комментарии к ней есть пример этой каши.
но и Кодд, и Дейт критиковали NULL в том виде, в котором они попали в стандарт:https://en.wikipedia.org/wiki/Null_(SQL)#Criticisms
Статья в Вики в Вашей ссылке начинается вот с этого (еще раз спасибо за ссылку):
In SQL, null or NULL is a special marker used to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information".
В SQL значение null (NULL) — это специальный маркер, используемый для обозначения отсутствия значения в базе данных. Введённый создателем реляционной модели баз данных Э. Ф. Коддом, SQL-термин null служит для выполнения требования, согласно которому все системы управления реляционными базами данных (СУРБД) должны поддерживать представление «отсутствующей информации и неприменимой информации».
Из чего следует что NULL это элемент реляционной модели, но не SQL, языка. А вот проблема NULL это проблема SQL.
Т.е. в итоге ни Кодд ни Дейт ничего не критиковали и критиковать не могли. Ничего кроме некоторых интерпретаций (трактовок) NULL в SQL.
Для меня есть некоторое подмножество SQL, необходимое и достаточное для всего что нужно чтобы манипулировать данными на уровне базы данных, реляционной или нет, не важно. SQL и к нереляционным БД можно пристегнуть, я знаю пример.
После того как с манипуляцией данными в БД процесс заканчивается и остаются вопросы в дело должен вступать язык другого уровня, уровня логики не покрываемой реляционной моделью данных. Таких языков много, начиная с Кобол, и заканчивая я даже не знаю чем.
Тенденция же такова что в SQL кто-то решил запихать всё. Впихнуть невпихуемое. И тут начались проблемы.
отдел есть, но мы не знаем какой ....
Это, извините, не о чем. Это Вам к "высоколобым надмозгом". С моей стороны no comments. Смотрим дальше.
null обычно трактуется как
Я привел ниже как трактуется NULL в руководстве DB2 for z/OS. К этой трактовке вопросы есть?
так вот, экстремальная нормализация позволяет нам отказаться от null вообще. ....
Это конечно оригинальный подход. Нет слов. Но и с IS NULL, and IS NOT NULL эта проблема тоже прекрасно решается.
Что касаемо "экстремальной" нормализации, то я скоро напишу статью про классическую нормализацию от тех кто на самом деле стоял у истоков реляционной технологии БД, а не от "высоколобых....", а более того если Вы имеете в виду пресловутую НФ 6, то даже там про это ничего нет.
проблемы неочевидности трактовки null'а можно избежать.
Нет никакой проблемы неочевидности трактовки null. Ну может только в "высоколобых надмозгах". В нормальных мозгах все с null очевидно. И нет никакой связи null с нормализацией.
Я привел ниже как трактуется NULL в руководстве DB2 for z/OS. К этой трактовке вопросы есть?
разумеется. там же просто пересказан стандарт.
вот рядом примерно то, что я написал, но другими словами:
https://habr.com/ru/articles/937964/comments/#comment_28723754
разумеется. там же просто пересказан стандарт.
Нет это не пересказ стандарта, это то как трактуется (так и только так) NULL в базе данных, которая была создана на принципах сформулированных теми кто "изобрел" реляционную модеоь и парадигму и на которой и создавался этот стандарт.
Это конечно не мешает другим БД допускать другие трактовки (типа NULL===NULL), но это уже проблемы других БД и тех кто ими пользуется.
Можно нормализовать NULL например в (-1)
WHERE coalesce(dept,-1) NOT IN (1,2);
Не будет работать индекс на dept, если он есть, но в запросе NOT IN индекс и не должен использоваться.
Я наткнулся на это довольно давно и больше скажу, мне попадались СУБД, где NULL==NULL. Возможно это была MSSQL
Это наверное потому что MSSQL тихой сапой допускает что NULL это бланк, пробел. Что не есть так.
Это не так. Вы путаете с Oracle
В MSSQL есть устаревший режим set ansi nulls off где можно сравнивать null на равенство
Хорошо, спасибо за уточнение.
Я не с MS SQL ни с Оракл всерьез не занимаюсь, но поддерживаю реприкацию из Оракл в MS SQL и наш DBA по этим базам мне то и дело показывает как что-то работает вот так вот тут и не так вот там. Про set ansi nulls off он тоже вспоминается говорил. Лично я считают это не допустимо. Подход к NULL должен быть един в БД, а не на выбор. Это приводит к плохим последствиям в использовании приложений.
Всерьез я работаю только с DB2 for z/OS и у меня никогда не возникало проблем и недопониманий по поводу темы статьи. Все очень четко и однозначно.
Это лечится включением параметра ANSI_NULLS на MS SQL. Добавлено было очень давно, где в 2008 версии.
Ой, подумаешь, два NULL равны, ерунда какая. Вот когда NULL равен пустой строке - это да...
NULL==NULL
Логично же, раз NULL - это не ноль, а непонятно что. Тогда название неправильное, да.
А так чего не сделать ?
... WHERE dept not IN (1, 2) or dept is null
Зачем вложенный запрос
"Для человека, не изучавшего в школе физику, мир полон чудес" ;-)
Документация:
https://www.postgresql.org/docs/17/functions-comparisons.html
9.2. Comparison Functions and Operators
Do not write expression = NULL because NULL is 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 IN
Note 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 IN construct 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 y is equivalent to NOT (x IN y) in all cases. However, null values are much more likely to trip up the novice when working with NOT IN than when working with IN. It is best to express your condition positively if possible.
Дело в том, что мы, опять-таки, забыли про трехзначную логику. У логических операторов не два, а три результата, а именно: ИСТИНА, ЛОЖЬ и НЕ ЗНАЮ.
Это интересно в каких логических операторах результат может быть "НЕ ЗНАЮ". Я таких не знаю. Это может быть в операторах сравнения если сравниваемые значения содержат NULL может получиться "НЕ ЗНАЮ", но строки с такими значениями вообще то исключаются до самих сравнений. Потому что значения NULL не сравниваемые ни с чем. Даже друг с другом.
В любых. Даже очевидный (not nulled_expression) is null в результате дает true. Так же как и true_expression and null и false_experssion or null в результате дают null aka boolean unknown. И строки выборки исключаются после вычисления условий их отбора, а не до - заглядывать в будущее даже оптимизаторы SQL пока не умеют.
Это интересно в каких логических операторах результат может быть "НЕ ЗНАЮ". Я таких не знаю.
ХЗ, случайно это получилось, или это такая тонкая ирония, но это пять, адназначна.
Дело в том, что значение 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;
А ещё есть is distinct from, которое нормально работает с null
Там у NULL все операции работают по-особенному. Именно поэтому и нужно всегда IS NULL проверять его, а не другим способом. Как тут уже выше писали, мир полон физики, если не знаешь магию. В статье хорошее напоминание об особенностях NULL, я считаю. Повторить пойдёт.
спасибо за статью! это ровно то чего всегда ожидаю от хабра - чтобы интересно и полезно, а ещё и с классной подачей написано!
такие статьи стали редкостью в потоке успешного успеха, корпоративной нативной рекламы и вайбкодинга :-)
Напрашивается еще какой-нибудь примерчик с COALESCE(...)
Те выходит что оператор not in (null, 1, 2) по идее всегда будет пустой датасет отдавать, потому что первое сранение всегда дает null, а дальше включается лен вая логика расчета логических выражений и проверок с 1 и 2 уже не будет?
Конечно неверно "not логическое выражение с null"
А верно должно быть "логическое выражение is not true"
Задачку можно добавить в собесы, определять тех кто базы вообще не понимает
Более простое объяснение это то, во что разворачивается 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.
А что же тогда выдаст второй запрос с NOT IN?
Поправьте запрос после данного текста. Скопировали, но не отредактировали.
Интересно было бы сравнить логику NULL в БД, нуллабельность и опционалы в языках программирования, а также поведение NaN (not a number) в числах с плавающей точкой. Что там общего, в чем различия и как лучше всего?
Сложности с NULL-ом в SQL это следствие ошибки проектирования стандарта. SQL просто очень древний, и тогда ещё не разобрались, что значение "нет значения" это не какая-то нештатная ситуация, а на редкость полезная фича. И это совсем не обязательно означает "значение неизвестно". Может быть очень даже известно, что его нет. Или значение известно, но его получение не прошло RLS. Или что угодно. В реляционной алгебре NULL-ы сплошь и рядом вылазят на outer join-ах даже если база идеально нормализована и все поля non-nullable.
Конечно же, NULL всегда должен быть равен NULL. Тернарная логика здесь не нужна никогда никому и ни для чего. За 30 лет работы с базами данных я набил очень много шишек об этот несчастный NULL, но ни разу этот наворот с NULL-ом не оказался полезен. Он всегда только мешает.
В других языках программирования (например Питон, JS, Rust) пустое значение равно пустому значению, и никто не жаловался и не просил срочно заменить на этот изврат с тернарной логикой.
И это совсем не обязательно означает "значение неизвестно". Может быть очень даже известно, что его нет.
Боюсь, что все ваши шишки за 30 лет - именно вот от этого дополнения про "известно, что его нет".
NULL - это именно "неизвестно", это маппирование UNKNOWN на синтаксис SQL.
А вот "значения нет" - это само по себе вполне определённое и известное значение, входящее в список возможных значений атрибута, со своим уникальным идентификатором, которое ничего общего с NULL не имеет. Часто это значение "не задано" или типа того, и часто с отдельной кодовой веткой его обработки.
Это вы уже в результате, полученном при выполнении запроса, можете интерпретировать NULL как "мне известно, что значения нет".
В других языках программирования (например Питон, JS, Rust) пустое значение равно пустому значению, и никто не жаловался и не просил срочно заменить на этот изврат с тернарной логикой.
Как правило, у вас найдётся возможность скорректировать поведение СУБД в нужную вам сторону. ANSI NULLS в SQL Server, null-safe compare operator <=> в MySQL и т.п.
А вот скорректировать наоборот, чтобы все NULL различались, когда они равны - это сложнее.
Как правило, у вас найдётся возможность скорректировать поведение СУБД в нужную вам сторону. 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.
NULL - это именно "неизвестно", это маппирование UNKNOWN на синтаксис SQL
NULL-ы, возвращаемые внешними соединениями и коррелированными подзапросами, имеют семантику именно "нет значения". Семантика "неизвестно" применима только для nullable полей, по которым договорились, что разрешается полениться их заполнять. Не то что бы совсем редкая ситуация, но всё же не основной мотив для nullability.
В примере в статье human.dept is null у господина Соболева скорее всего не потому что он неизвестно в каком департаменте, а потому что он пока ещё ни в каком департаменте. И это известно.
А вот "значения нет" - это само по себе вполне определённое и известное значение, входящее в список возможных значений атрибута, со своим уникальным идентификатором, которое ничего общего с NULL не имеет.
Магические константы для обозначения отсутствующих значений разных типов? Шутите?
ANSI NULLS
Здесь уже написали, что это безумие. От себя добавлю, что меня не радует перспектива разъяснять эту особенность каждому новому участнику проекта под роспись.
Тернарная логика NULL-ов это одна из тех вещей, про которые говорят "ошибка на миллиард долларов". С учётом древности истории, распространённости технологии и объёма вызываемого этим решением геморроя, думаю, совокупных убытков уже сильно больше, чем на миллиард. Во всём остальном SQL чудо как хорош, и похоже на то, что он теперь с нами навсегда. Ошибку на миллиард так просто не исправить, и поэтому единственное, что нам реально остаётся, это страдать и добавлять свои копеечки к совокупным убыткам.
Как правило, у вас найдётся возможность скорректировать поведение СУБД в нужную вам сторону
не такое уж и правило. постгрес входит в топ-3, в нём сходу вспоминаются только coalesce/is null.
скорее проблема от смешения «нет значения» и «значение неизвестно». задумывался null для второго, а используется чаще для первого
Оператор «NOT IN» и коварный NULL