Есть вещи, которые не нравятся, есть вещи, которые бесят, есть вызывающие жгучий гнев, и есть ситуация с NULL в SQL.
Догма
Мы все выучили наизусть: NULL не равен NULL. И не неравен NULL. И не больше, и не меньше. И не не больше, и не не меньше. Там вот такая вот особая хтонь, про которую надо постоянно помнить и которую надо постоянно аккуратно бочком обходить. Почему так всё причудливо, вам, конечно, с удовольствием объяснят. Дело в том, что NULL это неизвестность. Тень мирового зла. Каждый NULL это маленькая смерть.
Если ты допустил NULL в своей базе, ты грешен, и нет тебе пощады. Ты должен и будешь страдать. Капля неизвестности отравляет всю реку, взгляд в неизвестность ослепляет, размышление о неизвестности лишает разума.
Любая операция с неизвестностью (кроме специально выкованных эльфами операторов проверки на неизвестность) должна давать неизвестность. Это догма. Попытка оспорить догму - ересь.
Но давайте всё же попытаемся разобраться в вопросе, не особо стесняясь задавать неудобные вопросы.
В чём проблема
Проблема в том, что NULL в SQL это сейчас единственное место в информационных технологиях, где реально "в Проде" применяется троичная логика. Всё было бы ничего, но:
Далеко не все об этом догадываются, и работают с этим как с обычной двоичной логикой, отягощённой некой загадочной "фичей".
Троичная логика не применяется нигде кроме вычисления условий. Вне условий результат UNDEFINED интерпретируется как FALSE. Даже внутри самого SQL.
Всё остальное окружение – бэкенды, фронтенды, аналитические инструменты, мозги разработчиков, мозги пользователей – всё приспособлено работать с двоичной логикой, а с троичной логикой работает через пень колоду.
В родной двоичной у нас так:
A and B | A or B | ||||||||
A | not A | A \ B | false | true | A \ B | false | true | ||
false | true | false | false | false | false | false | true | ||
true | false | true | false | true | true | true | true | ||
В троичной всё причудливее:
A and B | A or B | ||||||||||
A | not A | A \ B | false | NULL | true | A \ B | false | NULL | true | ||
false | true | false | false | false | false | false | false | NULL | true | ||
NULL | NULL | NULL | false | NULL | NULL | NULL | NULL | NULL | true | ||
true | false | true | false | NULL | true | true | true | true | true | ||
И дополнительно:
A | A is NULL | A is not NULL | A is TRUE | A is FALSE |
false | false | true | false | true |
NULL | true | false | false | false |
true | false | true | true | false |
По сути, внутри вычисления условия применяется троичная логика, но потом к результату применяется "is TRUE", чтобы перепрыгнуть в нормальную двоичную логику, используемую дальше.
Потому что никто понятия не имеет, как сделать систему, целиком основанную на троичной логике.
Ситуацию можно было бы посчитать нормальной, если бы SQL был экспериментальным изделием, на котором академическое сообщество обкатывает безумные идеи. Но это уже давно далеко не так.
Вообще изучать эту троичную логику одно удовольствие. Она интересна, красива. Но когда переходишь к практическому использованию в реальных проектах, слов, кроме матерных, не остаётся.
А насколько вообще нужен NULL?
Может быть можно и без него? Тогда бы и проблема троичной логики ушла бы сама собой.
На это есть два соображения – одно практическое, другое глубоко теоретическое:
С практической точки зрения NULL – чрезвычайно удобная штука (если бы не троичная логика, конечно). Идеальный вариант для значения по умолчанию. Если по какой-то причине, коих кроме незнания ещё штук сто, нужно воздержаться от вписывания значения, NULL – лучший кандидат. Гораздо лучше, чем вписывать фейковое значение "родного" типа.
С точки зрения математической основы реляционных баз данных nullable колонки даже намного естественнее и роднее, чем "not null".
Раскрою второй пункт подробнее.
Почему NULL полноправный гражданин в реляционных базах данных
Знаете почему реляционные базы данных называются реляционными? Реляция (relation) это связь, отношение. Когда говорят, что база реляционная, связь чего с чем имеется в виду? Я провёл небольшое исследование и опросил коллег. Все они неплохие разработчики, давно имеющие дело с базами данных, и все мне ответили, что термин "relational" делает акцент на том, что там есть не только таблички, но и связи между ними по внешнему ключу. Стрелочки на Entity-Relationship (ага, вот оно!) диаграммах. Что, конечно же, неправильно.
Отношениями в теории реляционных баз данных являются не связи между таблицами, а сами таблицы. И даже не сами таблицы, а подмножества их колонок.
Рассмотрим такую таблицу:
id | Фамилия | Имя | Отчество |
1 | Иванов | Сергей | Петрович |
2 | Smith | John | |
... |
Она задаёт отношения (те самые relations) между следующими множествами:
Множество "сущностей", представленное колонкой
id.Множество фамилий.
Множество человеческих имён.
Множество отчеств.
Факты, сохранённые в строчках, можно сформулировать так:
Человек #1 имеет фамилию Иванов, имя Сергей, отчество Петрович.
Человек #2 имеет фамилию Smith, имя John, отчество... ээээ... [смущается, плачет]... давайте впишем NULL.
Можно заметить, что по сути, каждая строчка содержит по три отдельных факта вида "человек #1 имеет фамилию Иванов", "человек #1 имеет имя Сергей" и т.д. Ах да, вторая строчка не содержит факта про отчество.
С точки зрения реляционной теории мы вполне вправе разделить таблицу persons на четыре отдельные: собственно persons, в которой остаётся только колонка id, и три связанные с ней по внешнему ключу таблицы person_lnames, person_fnames и person_mnames. Когда будет нужно вытащить всё вместе, будем вязать "колоночные" таблицы к основной внешним соединением. Что, конечно, не так удобно, но сути происходящего не меняет. За исключением того, что структура с четырьмя таблицами эквивалентна исходной только в том случае, если атрибуты Фамилия, Имя и Отчество все nullable. Жёсткий эквивалент "not NULL" в конструкции с четырьмя таблицами организовать не получится. При этом, что интересно, сами "колоночные" таблицы полностью "not NULL", а пустые значения возникают в результате внешнего соединения. Например, для Джона Смита просто нет соответствующей записи в таблице person_mnames.
Избавиться от хранения значений NULL в колонках можно, но они всё равно будут возникать в результатах внешних соединений. А если избавиться от внешних соединений, получим неполноту реализации реляционной алгебры.
Итак, NULL это не ошибка, не нелепица и не результат раздолбайства, а вещь, естественным и необходимым образом присутствующая в идейной основе реляционных баз данных. Об этом, кстати, ещё даже великий Кодд писал.
NULL и троичная логика
Троичная логика не нужна. Без неё здесь можно обойтись точно так же, как мы без неё обходимся во всех остальных случаях. Двоичной логики "TRUE - FALSE" достаточно для любых применений. Без лишних усложнений. Для этого достаточно понять и принять два простых правила:
NULL равен NULL. Да, вот так, без глупостей. Единственное вполне объяснимое исключение – уникальный индекс на nullable колонке. Как мы помним, nullable колонка логически эквивалентна отдельной "колоночной" таблице, в которую в случае отсутствия значения просто не добавляется строка.
NULL меньше любого не-NULL значения. Почему не больше? Да просто потому что POLA (Principle of least astonishment).
Насколько мне известно, не существует ни одного рационального аргумента в пользу того, что результат сравнения "NULL = NULL" должен быть чем-то кроме TRUE. Только всякое бессмысленное словоблудие об инфернальной природе пустоты. Больше нигде – ни в науке, ни в технике, ни в культуре, ни в искусстве нет такого, чтобы отношение идентичности "is" было слабее отношения равенства.
Что касается практической полезности троичной логики, то полезность эта отсутствует. По крайней мере, я не могу вспомнить ни одного случая, когда эта самая троичная магия мне для чего-то пригодилась. Она всегда помеха, которую приходится преодолевать.
Единственное, для чего эти пляски вокруг NULL по-настоящему пригождаются, это чтобы трахать студентов на зачётах и экзаменах.
Выводы
Нужно признать, что решение применить троичную логику для NULL было концептуальной ошибкой, последствия которой индустрия разгребает уже несколько десятилетий, каждый день тратя уйму ресурсов и проливая тонны пота и слёз. И не имея взамен ровным счётом ничего.
Спасибо, господа академики, опыт был интересным и поучительным. Теперь пора исправить ошибку и двигаться дальше. В любом случае, рано или поздно, ошибку придётся исправлять.
Идея для стартапа: форкнуть Постгрес и сделать Postgres BFE (Bullshit-free Edition). И посмотреть, как народу зайдёт такая еретическая идея. Да, не соответствует стандарту SQL, ну так это... стандарты надо нормальные делать.