NULL и типы данных: где все спотыкаются

← назад к разделу

Есть тема, о которую спотыкаются все новички без исключения, — NULL. Запрос выглядит правильно, а возвращает не то; счётчики расходятся; строки исчезают из результата. В девяти случаях из десяти под капотом NULL. Разберёмся с ним и заодно с типами данных — второй половиной тех же граблей.

NULL — это «неизвестно», а не нольспросят на собеседовании

NULL — отметка «значения нет». Это не ноль, не пустая строка '', а именно отсутствие: у заказа не заполнена дата доставки, у клиента не указан телефон. И у NULL особая логика: любое сравнение с ним даёт не истину и не ложь, а «неизвестно».

живой пример

SELECT * FROM orders WHERE paid_at = NULL;   -- всегда пусто!

Этот запрос не вернёт ни строки — даже если неоплаченных заказов тысячи. «Равно неизвестно чему?» — «неизвестно», и строка в результат не попадает. Для NULL есть специальные операторы:

SELECT * FROM orders WHERE paid_at IS NULL;      -- заказ не оплачен
SELECT * FROM orders WHERE paid_at IS NOT NULL;  -- оплата прошла

Коварнее другое: NULL прячется и в отрицаниях. WHERE status <> 'PAID' вернёт заказы со статусом, отличным от PAID, — но не вернёт заказы, где статус NULL: сравнение с неизвестным не истинно. Хотите «все, кроме оплаченных, включая пустые» — пишите WHERE status <> 'PAID' OR status IS NULL.

Ещё три места, где NULL меняет результат:

• Агрегаты игнорируют NULL: отсюда разница COUNT(*) и COUNT(paid_at) из статьи про агрегаты; AVG считает среднее только по заполненным.
• LEFT JOIN порождает NULL в столбцах таблицы, где не нашлось пары, — мы пользовались этим для поиска «сирот».
• Арифметика с NULL даёт NULL: total_amount + NULL — это NULL, и сумма «испаряется».

Подставить замену вместо NULL умеет функция COALESCE: COALESCE(paid_at::text, 'не оплачен') вернёт дату оплаты, а при её отсутствии — заглушку. Два двоеточия здесь — приведение типа, «считай это значение текстом». Без них не обойтись: COALESCE обязан вернуть один тип на все свои аргументы, а дата и слово «не оплачен» сами по себе не сводятся — база откажется выполнять запрос. Превратили дату в текст — сводятся.

Рядом живут две функции, которые закрывают ту же дыру с других сторон.

IS DISTINCT FROM — это «не равно», умеющее сравнивать с пустотой. Обычное <> на NULL отвечает «неизвестно», а эта форма честно говорит «да, значения разные»:

живой пример

SELECT 'PAID' <> NULL AS obychnoe_sravnenie,
       'PAID' IS DISTINCT FROM NULL AS razlichayutsya;

Первая колонка придёт пустой (то самое «неизвестно»), вторая — true. Отсюда короткая запись для «все, кроме оплаченных, включая пустые»: WHERE status IS DISTINCT FROM 'PAID' вместо связки с OR status IS NULL. Есть и зеркальный IS NOT DISTINCT FROM — «равны, считая два пустых равными».

NULLIF(a, b) делает обратное: возвращает NULL, если a равно b. Главное его применение — защита от деления на ноль. Средний чек по оплаченным заказам без неё падает:

живой пример

SELECT customer_id,
       round(sum(total_amount) / NULLIF(count(paid_at), 0), 2) AS per_paid_order
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

У cus-09 три заказа и ни одного оплаченного: count(paid_at) даёт ровно ноль, и без NULLIF весь запрос падает с division by zero. С ней делитель превращается в NULL, деление даёт NULL, и в отчёте у этого покупателя просто пустая клетка. Обратите внимание на разницу: ноль ломает запрос, а NULL — нет.

Отработать здесь: Когда деньги реально дошли · Ярлык оплаты для каждого заказа · Лента оплат: неоплаченные — в самом низу

Ловушка NOT IN: один NULL — и результат пустспросят на собеседовании

Самое коварное следствие трёхзначной логики прячется в NOT IN с подзапросом. Запрос «клиенты без заказов»:

живой пример

SELECT * FROM customer
WHERE id NOT IN (SELECT customer_id FROM orders);

вернёт ноль строк, если в orders.customer_id есть хотя бы один NULL. Причина: NOT IN разворачивается в цепочку id <> v1 AND id <> v2 AND …, а сравнение id <> NULL даёт «неизвестно» — и вся цепочка становится «неизвестно» для каждой строки. Ошибка молчаливая: запрос синтаксически корректен и просто возвращает пусто.

Правильная форма — NOT EXISTS:

живой пример

SELECT * FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Она корректно работает с NULL и обычно даёт лучший план выполнения. Практическое правило: IN с подзапросом терпим, NOT IN с подзапросом не используйте никогда.

Сортировка с NULL: NULLS FIRST и NULLS LAST

Отсортировали заказы по дате оплаты, и вверху оказались неоплаченные, у которых даты нет вовсе. PostgreSQL считает NULL больше любого значения: при ORDER BY paid_at он идёт последним, при ORDER BY paid_at DESC первым. Ни то, ни другое не ошибка, но отчёт «сначала свежие доставки» с пустыми строками сверху выглядит сломанным.

Положение NULL задают явно, отдельно от направления:

живой пример

SELECT id, paid_at
FROM orders
ORDER BY paid_at DESC NULLS LAST;

NULLS FIRST и NULLS LAST пишутся после каждой колонки, у которой это важно. Индекс по колонке хранит NULL в порядке по умолчанию, поэтому DESC NULLS LAST иногда заставляет базу сортировать вместо того, чтобы читать индекс; для частых запросов индекс создают с тем же порядком: CREATE INDEX ... ON orders (paid_at DESC NULLS LAST). Другие базы ведут себя иначе: MySQL ставит NULL первым при возрастании, и это ещё одна причина писать положение явно.

Откуда берётся пустота

Может ли колонка быть пустой — решает не данные, а схема. NOT NULL запрещает пустоту: строку без значения база не примет. DEFAULT подставляет своё, когда значение не прислали, — поэтому created_at заполняется сам, хотя в запросе его никто не передавал. У заказа в песочнице status объявлен NOT NULL, а paid_at — нет, и это не случайность: заказ без оплаты существует, а заказ без статуса — нет.

Отсюда практический порядок действий в незнакомой таблице: сначала посмотреть, какие колонки вообще могут быть пустыми, потом писать условия. Показывает это \d orders в psql или колонка is_nullable в information_schema.columns. Как объявляют ограничения — в статье про схему, ключи и представления.

И одно следствие, о которое спотыкаются регулярно: ограничение UNIQUE пустоту не ловит. NULL не равен NULL, поэтому в колонке UNIQUE без NOT NULL спокойно уживутся хоть три строки с пустым значением — дублями они не считаются, и ограничение на них молчит. «Одно значение или ничего» даёт только пара UNIQUE + NOT NULL (а с PostgreSQL 15 ещё и форма UNIQUE NULLS NOT DISTINCT, где второй NULL уже считается дублем).

Типы: почему '10' — не 10спросят на собеседовании

Отчёт отсортировал суммы как 10, 2, 20, 9, и это не сбой сортировки: столбец строковый, а строки сравниваются посимвольно, и '9' > '10' — правда, девятка «больше» единицы. Проверить можно без таблицы: SELECT '9' > '10'; вернёт true, а SELECT unnest(ARRAY['9','10','2','20']) ORDER BY 1; выстроит значения именно так. У каждого столбца есть тип, и сравнивать нужно в своём типе: числа (integer, numeric) без кавычек, total_amount > 1000; строки (varchar, text) в одинарных кавычках, с точностью до регистра и пробелов; булев тип как WHERE is_active = true, а в базах, где вместо него числа 0/1, смотрите на реальные данные.

Даты и время (date, timestamp) пишут в кавычках в формате '2026-07-08', а диапазоны берут сравнениями: WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01' даёт все записи июля. Приём «начало следующего месяца, строго меньше» честно захватывает записи за последний день с временем 23:59:59. А BETWEEN для столбцов с временем ловушка: он включает обе границы, и BETWEEN '2026-07-01' AND '2026-07-31' обрежет весь последний день, кроме его полуночи. Почему так выходит — в статье про даты и время.

Отдельная строка про деньги: их держат в точном десятичном типе (numeric), а не в float, потому что дробные двоичные типы хранят значение приблизительно, и на тысяче копеечных слагаемых отчёт расходится с бухгалтерией. Подробно, с примером расхождения, — в статье про агрегаты.

Запись, созданная в 01:30, нашлась в базе вчерашним числом. Это, скорее всего, не баг, а часовой пояс: timestamp хранится в UTC, а интерфейс показывает местное время, и на границе суток даты расходятся.

Где это применяется

NULL — это карта минных полей продукта: незаполненные поля — ровно те места, где интерфейс, отчёты и интеграции ведут себя неожиданно. Запрос SELECT COUNT(*) FROM t WHERE важное_поле IS NULL — экспресс-аудит качества данных в любой таблице. А понимание типов избавляет от ложных тревог: «сортировка сломалась» (нет, столбец строковый), «время на час уехало» (нет, это часовой пояс).

Где спотыкаются начинающие:

• Пишут = NULL вместо IS NULL — запрос молча возвращает пустоту, и кажется, что данных нет.
• Забывают, что <> не находит NULL. «Все кроме оплаченных» без OR status IS NULL теряет строки — и никто не замечает.
• Сравнивают дату-время с «просто датой» и удивляются: WHERE created_at = '2026-07-08' не находит записи за день — потому что сравнивает с полуночью. Диапазон надёжнее: WHERE created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + 1, где CURRENT_DATE — сегодняшняя дата по часам самой базы. Про такие выражения — отдельная статья про даты и время.

Коротко

• NULL это «неизвестно»: сравнение с ним даёт не истину и не ложь, искать его можно только через IS NULL.
• NOT IN со списком, в котором есть NULL, возвращает пусто; безопаснее NOT EXISTS.
• Текст '10' и число 10 разные типы: сравнение через приведение отключает индекс, колонки объявляют правильным типом.
• В сортировке NULL больше всего: положение задают явно, NULLS FIRST или NULLS LAST.
• IS DISTINCT FROM сравнивает с пустотой без трёхзначной логики; NULLIF(a, b) спасает от деления на ноль, превращая ноль в NULL.
• Пустоту разрешает схема: NOT NULL запрещает, DEFAULT подставляет; UNIQUE без NOT NULL дублей из пустых значений не ловит.

Что почитать дальше

• Даты и время — типы, где ошибки типов встречаются чаще всего.
• Строки — NULL при склейке и приведении текста.
• Подзапросы — NOT EXISTS вместо NOT IN.