Комментарии 11
NULL≠0(ноль - это число);
Некорректно. Если в данном случае NULL - литерал, то пояснение правильное, но если это значение поля записи числового типа, то он вполне себе число, ибо тип и значение есть разные атрибуты. Хотя он по-прежнему не равен нулю.
FALSEANDUNKNOWN=FALSE(это неинтуитивно, но так работает стандарт SQL
Да вполне себе интуитивно. Чтобы получить TRUE, нужно, чтобы все соединяемые через AND значения были TRUE.
В
WHERE,CHECKусловие считается истинным, только если выражение вернулоTRUE. Если вернулосьFALSEилиUNKNOWN— строка отклоняется / ограничение не нарушается (потому что для нарушения нужноFALSE, а неUNKNOWN).
Ну это вы сильно запутаете того, кто не в теме. Лучше распишите по отдельности, что WHERE требует TRUE, тогда как CHECK требует что угодно, лишь бы не FALSE.
SUM, AVG и COUNT(column) тихо игнорируют NULL.
Следует оговорить, что если все значения в группе есть NULL, то агрегатная функция вернёт-таки NULL. А ещё я бы добавил, что COUNT как раз NULL возвращать не умеет, что для начинающих тоже не всегда очевидно.
В PostgreSQL и свежих версиях SQL Server уже появился оператор
IS [NOT] DISTINCT FROM.
Они не единственные. В MySQL/MariaDB давным-давно есть два разных оператора сравнения - обычное compare ( = ) и null-safe compare ( <=> ).
Решение: используйте
NOT EXISTS:
Увы, неуниверсально. Далеко не всегда обычный подзапрос можно преобразовать в коррелированный (пусть это и нечастая ситуация). К тому же далеко не факт, что такое преобразование не скажется самым фатальным образом на плане выполнения.
1. «NULL ≠ 0 - если это поле числового типа, то он вполне себе число»
Вы смешиваете тип данных и значение. Да, поле имеет числовой тип, но само значение NULL не является числом в математическом смысле. Это маркер отсутствия числа в данном поле.
2. «FALSE AND UNKNOWN = FALSE - вполне себе интуитивно»
Тут сложно спорить, все индивидуально) Интуитивно для тех, кто знает теорию множеств и булеву алгебру. А новичок часто видит UNKNOWN и думает: «Ну, раз неизвестно, то и результат должен быть неизвестен». А тут вдруг FALSE.
3. «WHERE требует TRUE, CHECK требует что угодно, лишь бы не FALSE».
Вопрос формулировок. Надеюсь, это замечание окончательно закрепит понимание этого важного нюанса)
4. «SUM, AVG игнорируют NULL - надо оговорить, что возвращают NULL, если все NULL»
Да, это следует из определения агрегации. Если нет значений - нечего суммировать.
5. «В MySQL есть <=> они не единственные»IS NOT DISTINCT FROM (как и его "обратная" версия IS DISTINCT FROM) – включен в стандарт ANSI SQL. В то же время MySQL и MariaDB реализуют ту же логику через свой собственный оператор <=>, который не является стандартным, и при миграции кода может вызвать проблемы.
6. «NOT EXISTS - неуниверсально, может убить план»
Этот пункт конкретно про рекомендацию против логической ошибки с NULL и NOT IN, а не как догма для всех случаев. План выполнения не имеет значения, если результат неверный. Про эту тему можно добавить:
· Современные оптимизаторы (PostgreSQL, Oracle, SQL Server) умеют преобразовывать NOT EXISTS в анти-соединения и хеш-соединения, если это выгодно (не всегда и не все). В любом случае необходимо смотреть план выполнения в каждом запросе, а не следовать бездумно общим рекомендациям.
· Если подзапрос некоррелированный, его можно вынести в CTE или материализовать, или решить другим способом. Это вопрос не навыка работы с NULL.
В любом случае, мы рады что эта тема вызвала Ваш интерес. Ваши замечания, надеемся, катализирует читателя глубже разбирать формулировки и крайние случаи.
Вы смешиваете тип данных и значение. Да, поле имеет числовой тип, но само значение NULL не является числом в математическом смысле. Это маркер отсутствия числа в данном поле.
Я же рассматриваю вполне себе конкретную частную ситуацию. Есть конкретная таблица, в ней есть конкретное поле числового типа, есть конкретная запись, где в этом поле хранится NULL. А вы опять говорите об абстрактной ситуации. Да, определённого числового значения нет, есть NULL как маркер отсутствия значения. Но тип значения у этого маркера отсутствия в описанном случае - есть. И он будет использоваться, несмотря на отсутствие значения - например, если значение является одним из операндов функции COALESCE.
Вопрос формулировок.
Я не о формулировках, а об их смешении в одном предложении, чего настоятельно советую избегать в данном конкретном случае.
Да, это следует из определения агрегации. Если нет значений - нечего суммировать.
Но COUNT-то NULL не возвращает, а возвращает честный числовой ноль. И плевать ему, что нечего подсчитывать. Почему я и говорю, что об этом бы стоило сказать отдельно и явно - именно для начинающих.
MySQL и MariaDB реализуют ту же логику через свой собственный оператор
<=>, который не является стандартным
Отож. Когда они реализовали этот оператор, никаким IS NOT DISTINCT FROM в стандарте ещё даже не пахло.
Я же рассматриваю вполне себе конкретную частную ситуацию. Есть конкретная таблица, в ней есть конкретное поле числового типа, есть конкретная запись, где в этом поле хранится NULL. […]
То, что вы рассматриваете конекретную ситуцию, не делает NULL и 0 одним и тем же значением. Что влияет, к примеру, на результат функции COALESCE.
По-моему, вы меня неправильно понимаете. Я не оспариваю результат сравнения.
Я утверждаю то, что ваше пояснение в скобках ("ноль - это число") не является корректным обоснованием причины неравенства. Из вашего этого пояснения неявным образом формируется вывод, что "а NULL - это НЕ число". И вот этот вывод - он как раз и некорректен, потому что NULL в некоторых ситуациях вполне себе может иметь определённый тип данных. Как в описанном случае, когда этот тип явно определяется типом поля, так и в случае неявного определения типа, если NULL является результатом вычисления функции, и тогда тип значения определён типом возвращаемого функцией значения. В СУБД со строгой типизацией при этом можно даже схватить ошибку сравнения type mismatch, если тип возврата функции не числовой (впрочем, скорее всего эта ошибка вылезет ещё на стадии парсинга).
Последний пункт отлично иллюстрирует исходное замечание: в каком-нибудь double (в Oracle - binary_double, в pg - double precision) NaN - тоже "не число" с точки зрения математики, однако это не делает его равным или не равным NULL. В операторе сравнения тип обоих операндов определяется до выполнения самого сравнения (явно или неявным приведением), и в рамках этого типа значения NULL, 0, NaN являются просто разными как элементы множества значений типа.
del
Количество разночтений и правда обескураживает, упомнить их невозможно. Оно радует только HR на собесах, когда им надо безупречно завалить кандидата, потому что выбор уже сделан, “глядя жертве в глаза”.
В жизни чуть проще. В отделах аналитики - на один SQL-запрос приходится примерно 8-мь df.query / df.loc / df.at запросов на Python/Pandas. БД in-memory Pandas или паркеты/feather/pickles работают быстрее любых СУБД (на таблицах до 10M х 1k, коих обычно >96%). Но в пандах к питоновскому None прибавились типы pd.NA + np.nan + np.nat, что, казалось бы, запутало ситуацию еще больше. На практике оказалось что нет.
Т.к. Pandas это ETL/ELT - легко заменить все виды Null в данных на что-то типа “неизв.” или “#Н/Д” (с)Excel, и категоризовать столбец целиком, добавив несуществующую категорию pd.NA “чтобы было”. Да, Null обязательно вернется без стука, при Left/Right/Outer объединениях, квантайзах, наивных джойнах каких-нить 1С-данных из разных лет или платформ, с “поплывшей” от времени аналитикой. Но мы просто заменим df.filna(“неизв.”) еще раз (для временных рядов df.ffill() или df.bfill()) - и продолжим не заморачиваться по поводу зоопарка поведения Null-значений при любых запросах, агрегациях и сравнениях. Это проще чем помнить про Null и обходы с EXIST в разных диалектах SQL. Жаль что так вышло с SQL.
Поэтому не нужно на уровне схемы бд разрешать в таблицах null, тогда он будет появляться только в left/right join и там его уже проще не забыть используя coalesce на полях.
Увы, это не всегда возможно - сделать все поля NOT NULL. Ведь в этом случае вместо NULL вам необходимо иметь некий default value, который интуитивно понятен, проходит все CHECK, не ломает FK, гарантированно не бывает в рабочем наборе, и при этом не влияет на дальнейшую обработку без добавления специфических условий типа WHERE .. AND column <> 'не определено'.
Для строк Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. https://habr.com/ru/articles/127327/
Гадкий NULL