Как обрабатывать NULL значения в SQL? Приведите примеры с COALESCE, NULLIF, IS NULL.?

Question

claude-haiku-4.5 · Accepted Answer

## Обработка NULL значений в SQL

NULL в SQL — это отсутствие значения, отличается от пуста строки или нуля. Правильная работа с NULL критична для точных аналитических отчётов.

### Основная проблема NULL

```sql
-- NULL уникален: любое сравнение с NULL дает NULL, не TRUE или FALSE
SELECT NULL = NULL;      -- результат: NULL (не TRUE!)
SELECT NULL <> 5;        -- результат: NULL
SELECT 10 + NULL;        -- результат: NULL
SELECT NULL || 'text';   -- результат: NULL
```

Поэтому для работы с NULL используются специальные функции.

### 1. IS NULL и IS NOT NULL

Единственный способ правильно проверить NULL:

```sql
-- Найти все заказы без рефертуры скидочного кода
SELECT order_id, total_amount
FROM orders
WHERE discount_code IS NULL;

-- Найти пользователей с заполненным email
SELECT user_id, name, email
FROM users
WHERE email IS NOT NULL;

-- Комбинирование условий
SELECT user_id, name, last_purchase_date
FROM users
WHERE 
  last_purchase_date IS NOT NULL
  AND last_purchase_date > '2024-01-01';
```

### 2. COALESCE() — заменить NULL на значение

Возвращает первый НЕ-NULL аргумент:

```sql
-- Заменить NULL на 0
SELECT user_id, COALESCE(lifetime_value, 0) as ltv
FROM users;

-- Множественные fallbacks
SELECT 
  user_id,
  COALESCE(
    preferred_email,      -- используй основной email
    secondary_email,      -- или вспомогательный
    'no-email@example.com' -- или дефолт
  ) as contact_email
FROM users;

-- Практический пример: заполнение данных
SELECT
  user_id,
  COALESCE(
    phone_country_code,
    country_code,
    'US'
  ) as country
FROM users;
```

**Когда использовать COALESCE:**
- Замена NULL на дефолтное значение для отчёта
- Заполнение отсутствующих данных из альтернативных источников
- Подготовка данных к экспорту (некоторые системы не поддерживают NULL)

### 3. NULLIF() — превратить значение в NULL

Обратное COALESCE — возвращает NULL, если аргументы равны:

```sql
-- Пример: показать изменение цены, NULL если цена не изменилась
SELECT
  product_id,
  old_price,
  new_price,
  NULLIF(new_price - old_price, 0) as price_change
FROM price_history;

-- Результат:
-- product_id | old_price | new_price | price_change
--    101     |    50     |    55     |      5
--    102     |    40     |    40     |     NULL  ← не изменилась
--    103     |    80     |    75     |     -5
```

**Практическое применение:**

```sql
-- Избежать деления на 0 (которое выдаёт ошибку или бесконечность)
SELECT
  product_id,
  impressions,
  clicks,
  CASE 
    WHEN impressions = 0 THEN NULL  -- или можно использовать NULLIF
    ELSE clicks::FLOAT / impressions
  END as ctr
FROM ad_metrics;

-- Или более элегантно с NULLIF
SELECT
  product_id,
  impressions,
  clicks,
  clicks::FLOAT / NULLIF(impressions, 0) as ctr
FROM ad_metrics;
```

### 4. Комбинированные примеры

#### Пример 1: Анализ обработки заказов

```sql
-- Найти заказы, где не заполнена дата доставки
-- (в обработке или потеряны)
SELECT
  order_id,
  user_id,
  created_at,
  CASE 
    WHEN delivered_at IS NULL THEN 'pending'
    ELSE 'delivered'
  END as order_status,
  -- Дни в доставке (NULL если ещё не доставлен)
  NULLIF(
    EXTRACT(DAY FROM delivered_at - created_at),
    0
  ) as days_to_deliver
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days';
```

#### Пример 2: Объединение данных из двух источников

```sql
-- У пользователя есть данные в двух таблицах
-- Используем COALESCE для приоритета источников
SELECT
  COALESCE(u.user_id, up.user_id) as user_id,
  COALESCE(u.email, up.email) as email,
  COALESCE(u.country, up.country, 'Unknown') as country,
  u.premium_status,
  up.profile_completed
FROM users u
FULL OUTER JOIN user_profiles up ON u.user_id = up.user_id;
```

#### Пример 3: Выявление пропущенных данных

```sql
-- Report о качестве данных
SELECT
  'email_missing' as data_quality_issue,
  COUNT(*) as affected_rows,
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM users), 2) as pct_of_total
FROM users
WHERE email IS NULL
UNION ALL
SELECT
  'phone_missing',
  COUNT(*),
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM users), 2)
FROM users
WHERE phone IS NULL
UNION ALL
SELECT
  'both_missing',
  COUNT(*),
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM users), 2)
FROM users
WHERE email IS NULL AND phone IS NULL;
```

#### Пример 4: Агрегирующие функции и NULL

```sql
-- Важно: SUM, AVG, COUNT игнорируют NULL
SELECT
  user_id,
  COUNT(*) as total_purchases,              -- считает все строки
  COUNT(coupon_code) as purchases_with_coupon, -- считает только не-NULL
  SUM(amount) as total_spent,                  -- NULL в сумме игнорируется
  AVG(amount) as avg_purchase,                 -- NULL в среднем игнорируется
  COALESCE(AVG(amount), 0) as avg_or_zero
FROM purchases
GROUP BY user_id;

-- Результат:
-- user_id | total_purchases | purchases_with_coupon | total_spent | avg_purchase | avg_or_zero
--    1    |        5        |          2            |    500      |    100       |     100
--    2    |        3        |          0            |    100      |     33.33    |    33.33
```

### 5. IFNULL vs COALESCE (разные БД)

```sql
-- MySQL: IFNULL(column, replacement)
SELECT IFNULL(discount, 0) as discount FROM orders;

-- SQL Server: ISNULL(column, replacement)
SELECT ISNULL(discount, 0) as discount FROM orders;

-- PostgreSQL, SQLite, BigQuery: COALESCE
SELECT COALESCE(discount, 0) as discount FROM orders;
```

### 6. NULL в ORDER BY

```sql
-- NULL обычно сортируется в начало или в конец (зависит от БД)
SELECT user_id, premium_expiry
FROM users
ORDER BY premium_expiry DESC NULLS LAST;  -- NULL в конце

-- PostgreSQL: NULLS FIRST / NULLS LAST
-- MySQL: просто ORDER BY (NULL идет в начало)
-- SQLite: NULL идет в начало при DESC, в конец при ASC
```

### 7. Проверка NULL в JOIN

```sql
-- ВНИМАНИЕ: NULL = NULL это NULL, не TRUE!
-- Неправильно:
SELECT u.user_id, o.order_id
FROM users u
JOIN orders o ON u.referral_code = o.referral_code;
-- Пропустит все пары, где оба NULL!

-- Правильно:
SELECT u.user_id, o.order_id
FROM users u
JOIN orders o ON u.referral_code = o.referral_code
UNION ALL
SELECT u.user_id, o.order_id
FROM users u
JOIN orders o ON u.referral_code IS NULL AND o.referral_code IS NULL;
```

### Чеклист работы с NULL

- ☑ Используй IS NULL / IS NOT NULL, не = NULL
- ☑ COALESCE для замены NULL на значение
- ☑ NULLIF для превращения значения в NULL
- ☑ Помни: SUM, AVG, COUNT игнорируют NULL
- ☑ Проверяй COUNT(*) vs COUNT(column_with_nulls) — разные результаты
- ☑ JOIN не работает с NULL — нужна отдельная логика
- ☑ Документируй, какие NULL в твоих таблицах — отсутствующие данные или явное "нет"
- ☑ При GROUP BY NULL = NULL, поэтому NULL-ы группируются вместе

### Лучшие практики

1. **Минимизируй NULL** — если возможно, используй дефолтные значения при создании таблиц
   ```sql
   CREATE TABLE users (
     user_id BIGINT,
     email VARCHAR NOT NULL,
     phone VARCHAR DEFAULT '',  -- вместо NULL
     premium BOOLEAN DEFAULT FALSE
   );
   ```

2. **Документируй NULL** — что означает NULL в каждой колонке?
   - NULL = отсутствующие данные, не заполнено еще
   - NULL = явное "не применимо" для этого пользователя

3. **Тестируй граничные случаи** — включай NULL в тестах

4. **Используй правильно в CASE** — NULL может быть результатом
   ```sql
   CASE 
     WHEN column IS NULL THEN 'missing'
     WHEN column = 0 THEN 'zero'
     ELSE 'has value'
   END
   ```

Как обрабатывать NULL значения в SQL? Приведите примеры с COALESCE, NULLIF, IS NULL.?

Комментарии (1)

Обработка NULL значений в SQL

Основная проблема NULL

1. IS NULL и IS NOT NULL

2. COALESCE() — заменить NULL на значение

3. NULLIF() — превратить значение в NULL

4. Комбинированные примеры

Пример 1: Анализ обработки заказов

Пример 2: Объединение данных из двух источников

Пример 3: Выявление пропущенных данных

Пример 4: Агрегирующие функции и NULL

5. IFNULL vs COALESCE (разные БД)

6. NULL в ORDER BY

7. Проверка NULL в JOIN

Чеклист работы с NULL

Лучшие практики