0Pricing
Coding Interview Prep · Урок

NULL в агрегатах, объединениях и DISTINCT

Узнайте, как NULL по-разному ведёт себя при группировке, объединении и проверке уникальности.

«NULL в агрегатах, объединениях и DISTINCT» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.

NULL в трёх неожиданных местах

NULL ведёт себя неодинаково в разных случаях. В заключительном уроке рассматриваются три контекста, в которых его поведение чаще всего удивляет кандидатов: агрегатные функции, соединения и DISTINCT / GROUP BY.

Повторяющаяся особенность такова: агрегатные функции и фильтрация воспринимают NULL как «пропустить», а группировка и DISTINCT — как «значение, равное другим NULL». Именно эта непоследовательность интересует интервьюеров.

Освойте эти правила — и вы разберётесь с наиболее распространёнными вопросами о NULL на технических собеседованиях по базам данных.

Агрегатные функции игнорируют NULL

Главное правило: агрегатные функции пропускают NULL. SUM, AVG, MIN, MAX и COUNT(столбец) полностью игнорируют входные значения NULL, а не считают их нулём.

Поэтому AVG может вернуть не то число, которого вы ожидаете. Функция делит сумму значений, не равных NULL, на количество значений, не равных NULL, а не на общее число строк.

-- bonus values: 100, 200, NULL
SELECT
  SUM(bonus) AS total,   -- 300 (NULL ignored)
  AVG(bonus) AS average, -- 150 = 300 / 2, not / 3
  COUNT(bonus) AS cnt    -- 2 (NULL not counted)
FROM employees;

Сравнение COUNT(*) и COUNT для столбца

Самый частый вопрос об агрегатах и NULL. COUNT(*) считает строки, включая строки со значениями NULL. COUNT(column) считает только строки, в которых этот столбец не равен NULL.

Таким образом, разница между ними в точности равна количеству NULL в этом столбце. COUNT(DISTINCT column) идёт дальше: он также игнорирует NULL и одновременно удаляет дубликаты.

SELECT
  COUNT(*)              AS rows_total,    -- all rows
  COUNT(bonus)          AS non_null_bonus, -- excludes NULLs
  COUNT(DISTINCT bonus) AS distinct_bonus, -- excludes NULLs + dups
  COUNT(*) - COUNT(bonus) AS null_bonus
FROM employees;

AVG и SUM/COUNT(*): классическая ловушка

Интервьюеры спрашивают: «Равно ли AVG(x) выражению SUM(x) / COUNT(*)?» Ответ: нет, если есть NULL.

AVG(x) равно SUM(x) / COUNT(x): деление выполняется на количество значений, не равных NULL. Деление на COUNT(*) вместо этого считает NULL нулевыми значениями, занижая среднее.

Если вы действительно хотите считать NULL нулями, явно укажите это с помощью COALESCE.

-- These differ when bonus has NULLs:
SELECT
  AVG(bonus)                       AS avg_ignoring_nulls,
  SUM(bonus) * 1.0 / COUNT(*)      AS avg_nulls_as_zero,
  AVG(COALESCE(bonus, 0))          AS explicit_nulls_as_zero
FROM employees;

Особый случай агрегирования, когда все значения — NULL

Что возвращает агрегат, когда все входные значения равны NULL или строк нет? Вот точное различие, которое любят проверять интервьюеры:

  • SUM, AVG, MIN, MAX по значениям, равным NULL, или при нулевом количестве строк возвращают NULL.
  • COUNT всегда возвращает 0, никогда не NULL.

Поэтому если в отчёте отображаются пустые итоги, вероятная причина — SUM, состоящий только из NULL. Оберните его в COALESCE, чтобы отображать 0.

-- No matching rows or all bonuses NULL:
SELECT SUM(bonus) FROM employees WHERE 1 = 0;  -- NULL
SELECT COUNT(bonus) FROM employees WHERE 1 = 0; -- 0

-- Present a clean zero:
SELECT COALESCE(SUM(bonus), 0) FROM employees;

NULL в условиях JOIN

В условии ON при соединении NULL = NULL по-прежнему даёт UNKNOWN, поэтому ключи NULL никогда не сопоставляются при соединении по равенству. Две строки с NULL в ключе соединения не будут объединены.

Это часто случается при соединении по необязательным внешним ключам. Если сопоставление NULL со значением NULL является нужным поведением, используйте оператор с защитой от NULL (IS NOT DISTINCT FROM или <=>) из предыдущего урока.

-- Rows with region IS NULL on both sides do NOT match
SELECT *
FROM a JOIN b ON a.region = b.region;

-- To match NULL-to-NULL (ANSI):
SELECT *
FROM a JOIN b ON a.region IS NOT DISTINCT FROM b.region;

NULL, создаваемые внешними соединениями

Внешние соединения создают NULL для несопоставленных строк. После LEFT JOIN каждый столбец правой таблицы равен NULL для левых строк, для которых не найдено соответствие.

Это основа шаблона антисоединения: отфильтруйте WHERE right_table.key IS NULL, чтобы найти строки без соответствий, например клиентов без заказов.

Однако будьте осторожны: фильтрация столбца внешнего соединения в WHERE может случайно снова превратить его во внутреннее соединение — это тема следующей сцены.

-- Find customers who have never ordered (anti-join)
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Ловушка NULL при использовании WHERE с внешним JOIN

Популярная ловушка. Вы соединяете таблицу заказов с помощью LEFT JOIN, затем добавляете WHERE o.status = 'shipped'. Внезапно клиенты без заказов исчезают, и внешнее соединение фактически превращается во внутреннее.

Почему? Для несопоставленных строк o.status равен NULL, а NULL = 'shipped' даёт UNKNOWN, поэтому WHERE отбрасывает такие строки. Чтобы сохранить несопоставленные строки, перенесите условие в предложение ON.

-- Accidental inner join: drops customers with no orders
SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';

-- Correct: keep unmatched customers
SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'shipped';

DISTINCT считает все NULL одинаковыми

Вот противоречие, которое удивляет всех. Агрегатные функции пропускают NULL, но DISTINCT оставляет ровно один NULL, считая все NULL дубликатами друг друга.

Поэтому SELECT DISTINCT bonus для значений 100, 100, NULL, NULL возвращает две строки: 100 и NULL — и это всё. Два значения NULL объединяются в одно, хотя в других местах NULL = NULL даёт UNKNOWN.

-- bonus: 100, 100, NULL, NULL, 200
SELECT DISTINCT bonus FROM employees;
-- Returns: 100, 200, NULL  (the two NULLs become one row)

GROUP BY объединяет все NULL в одну группу

GROUP BY действует по тому же правилу, что и DISTINCT: все ключи NULL собираются в одну группу. Это противоположно логике сравнений, где NULL никогда не равны друг другу.

Таким образом, группировка по столбцу, допускающему NULL, даёт одну строку, представляющую все записи с ключом NULL; обычно именно это нужно для отчётности. Упомяните этот контраст между группировкой и сравнением, чтобы показать глубину понимания.

-- All employees with NULL department form ONE group
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;
-- A single row where department is NULL totals all of them

Основные тезисы для собеседования

Обобщение, которое производит впечатление на интервьюеров:

  • Агрегатные функции игнорируют NULL; AVG делит результат на COUNT(столбец), а не на COUNT(*).
  • COUNT(*) подсчитывает строки; COUNT(столбец) и COUNT(DISTINCT столбец) пропускают NULL.
  • SUM/AVG/MIN/MAX для пустого набора строк возвращают NULL; COUNT возвращает 0.
  • При соединениях ключи NULL никогда не сопоставляются; фильтрация столбца внешнего соединения в WHERE незаметно превращает его во внутреннее соединение.
  • DISTINCT и GROUP BY считают все NULL одинаковыми, что противоположно логике сравнений.

Одной фразой: «NULL игнорируется при агрегировании и сравнении, но объединяется в одну группу при удалении дубликатов».

Быстрая проверка

Проверьте различие между группировкой и агрегированием.

Итоги

Вы завершили изучение обработки NULL для собеседований:

  • Агрегатные функции пропускают NULL; AVG делит результат на количество значений, не равных NULL, а SUM для набора, состоящего только из NULL, возвращает NULL, тогда как COUNT возвращает 0.
  • COUNT(*) учитывает строки с NULL; COUNT(col) — нет, и разница равна количеству значений NULL.
  • Ключи соединения, равные NULL, никогда не сопоставляются; фильтрация столбцов внешнего соединения в WHERE может превратить его во внутреннее соединение.
  • DISTINCT и GROUP BY объединяют все NULL в одну группу, что противоположно логике сравнений.

Запомните правило: NULL игнорируется при агрегировании и сравнении, но объединяется в одну группу при удалении дубликатов. Одного этого понимания достаточно, чтобы ответить на большинство вопросов о NULL на собеседовании.

Часто задаваемые вопросы

Урок «NULL в агрегатах, объединениях и DISTINCT» бесплатный?

Да — полный текст урока «NULL в агрегатах, объединениях и DISTINCT» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «NULL в агрегатах, объединениях и DISTINCT»?

Узнайте, как NULL по-разному ведёт себя при группировке, объединении и проверке уникальности. Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «NULL в агрегатах, объединениях и DISTINCT»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Трёхзначная логика и UNKNOWN
  2. IS NULL, IS NOT NULL и безопасное сравнение с NULL
  3. COALESCE, NULLIF и ISNULL
  4. NULL в агрегатах, объединениях и DISTINCT
← Назад к Coding Interview Prep