Какие знаешь особенности работы со значением NULL в SQL?

Какие знаешь особенности работы со значением NULL в SQL?

NULL — это одна из самых коварных и часто неправильно понимаемых концепций в SQL. Это не просто "пустое значение", а совершенно специальное состояние, которое требует особого обращения.

Что такое NULL?

NULL — это специальное значение в SQL, которое означает:

• "Неизвестное значение"
• "Отсутствие значения"
• "Неприменимо"
• "Не определено"

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),        -- может быть NULL
    birth_date DATE           -- может быть NULL
);

INSERT INTO users VALUES
(1, 'Alice', 'alice@example.com', '123-456-7890', '1990-05-15'),
(2, 'Bob', 'bob@example.com', NULL, '1985-03-20'),      -- Нет телефона
(3, 'Charlie', 'charlie@example.com', '789-123-4560', NULL);  -- Неизвестна дата рождения

Правило 1: NULL = NULL это FALSE (не TRUE!)

Это самая важная особенность:

SELECT * FROM users WHERE phone = NULL;
-- Результат: НОЛЬ строк!
-- Потому что NULL = NULL не TRUE, а UNKNOWN

-- ПРАВИЛЬНО:
SELECT * FROM users WHERE phone IS NULL;
-- Результат: строка с Bob (id=2)

Почему? Потому что NULL означает "неизвестное значение". Неизвестное = неизвестному это UNKNOWN, а не TRUE.

SELECT 
    id,
    name,
    phone = NULL as "phone = NULL",
    phone IS NULL as "phone IS NULL"
FROM users;

id | name    | phone = NULL | phone IS NULL
---|---------|------|----------
1  | Alice   | NULL | false
2  | Bob     | NULL | true
3  | Charlie | NULL | false

Правило 2: Арифметика с NULL = NULL

Любая арифметическая операция с NULL результирует в NULL:

SELECT 
    id,
    salary,
    bonus,
    salary + bonus as total_salary
FROM employees;

id | salary | bonus | total_salary
---|--------|-------|-------------
1  | 5000   | 1000  | 6000
2  | 6000   | NULL  | NULL        ← NULL!
3  | NULL   | 500   | NULL        ← NULL!

Даже если salary = 6000 известна, если bonus = NULL, сумма = NULL:

SELECT 10 + NULL;  -- NULL
SELECT 5 * NULL;   -- NULL
SELECT NULL || 'text';  -- NULL (конкатенация)

Правило 3: Сравнение с NULL

SELECT * FROM products WHERE price > 100;
-- НЕ включит товары с price = NULL!
-- Потому что NULL > 100 это UNKNOWN, а не TRUE

SELECT * FROM products WHERE price > 100 OR price IS NULL;
-- Вернет товары дорже 100 И товары с неизвестной ценой

Правило 4: Логические операции

NULL в логических выражениях дает UNKNOWN (как третье значение):

WHERE status = 'active' OR deleted_at IS NOT NULL;

status   | deleted_at | результат
---------|-----------|----------
active   | NULL      | true      (первое условие true)
inactive | 2024-01-01| true      (второе условие true)
inactive | NULL      | false     (оба условия false)
active   | NULL      | true      (первое условие true)

AND с NULL:

SELECT * FROM users WHERE age > 18 AND city = NULL;
-- Если city = NULL, вторая часть UNKNOWN
-- true AND UNKNOWN = UNKNOWN (НЕ ВЕРНЕТ ничего)

-- ПРАВИЛЬНО:
SELECT * FROM users WHERE age > 18 AND city IS NULL;

Правило 5: COALESCE и IFNULL

Чтобы обработать NULL, используй функции:

-- COALESCE: возвращает первый НЕ-NULL аргумент
SELECT 
    id,
    COALESCE(phone, 'No phone') as phone,
    COALESCE(birth_date, '1970-01-01') as birth_date
FROM users;

id | phone            | birth_date
---|------------------|-----------
1  | 123-456-7890     | 1990-05-15
2  | No phone         | 1985-03-20
3  | 789-123-4560     | 1970-01-01  ← default

-- IFNULL (MySQL): если первый NULL, возвращает второй
SELECT IFNULL(phone, 'Unknown phone') FROM users;

-- NULLIF (все БД): если значения равны, возвращает NULL
SELECT NULLIF(price, 0) FROM products;  -- если цена 0, вернет NULL

Правило 6: ORDER BY с NULL

Порядок NULL в ORDER BY зависит от БД:

SELECT * FROM users ORDER BY birth_date;

-- PostgreSQL: NULL в конце (ASC) или начале (DESC)
id | name    | birth_date
---|---------|----------
3  | Charlie | NULL      ← в конце при ASC
2  | Bob     | 1985-03-20
1  | Alice   | 1990-05-15

-- MySQL: NULL всегда в начале (независимо от ASC/DESC)
id | name    | birth_date
---|---------|----------
3  | Charlie | NULL      ← в начале
2  | Bob     | 1985-03-20
1  | Alice   | 1990-05-15

-- Для контроля:
SELECT * FROM users 
ORDER BY birth_date IS NULL, birth_date;  -- NULL в конце везде

Правило 7: DISTINCT с NULL

SELECT DISTINCT city FROM users;

Да, NULL будет учитан как отдельное значение:
city
-----
NULL  ← одно значение NULL из всех NULL'ов
New York
Los Angeles

Правило 8: GROUP BY с NULL

SELECT city, COUNT(*) FROM users GROUP BY city;

Rезультат:
city         | COUNT
-------------|------
NULL         | 2     ← оба NULL'а в одной группе
New York     | 3
Los Angeles  | 1

Правило 9: JOIN и NULL

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- NULL в ON условии не объединяет!
u.id | o.user_id | результат
-----|-----------|----------
1    | 1         | ОБЪЕДИНИТ
2    | 2         | ОБЪЕДИНИТ
3    | NULL      | НЕ объединит (NULL != 3)

-- LEFT JOIN все равно вернет user_id=3 с NULL значениями order'ов

Реальный пример: Бюджет проекта

// Java код работающий с NULL
String sql = "SELECT " +
    "id, " +
    "name, " +
    "budget, " +
    "spent, " +
    "COALESCE(budget - spent, budget) as remaining " +
    "FROM projects " +
    "WHERE (spent IS NULL OR spent < budget) " +
    "ORDER BY budget IS NULL, budget DESC";

ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    
    // В Java NULL обычно проверяется явно
    Integer remaining = (Integer) rs.getObject("remaining");
    if (remaining != null) {
        System.out.println(name + ": осталось " + remaining);
    } else {
        System.out.println(name + ": неизвестное значение");
    }
}

Особенности конкретных БД

PostgreSQL:

-- COALESCE работает
SELECT COALESCE(phone, 'unknown') FROM users;

-- NULLS FIRST / NULLS LAST
SELECT * FROM users ORDER BY birth_date DESC NULLS FIRST;

MySQL:

-- IFNULL вместо COALESCE (хотя COALESCE тоже работает)
SELECT IFNULL(phone, 'unknown') FROM users;

-- Нет NULLS FIRST, NULL всегда в начале

SQL Server:

-- ISNULL
SELECT ISNULL(phone, 'unknown') FROM users;

-- ORDER BY с NULLS
SELECT * FROM users ORDER BY birth_date DESC NULLS FIRST;

Антипаттерны

-- ПЛОХО
WHERE status = 'active' OR status = NULL;  -- status = NULL НИКОГДА не true!
WHERE status != 'deleted';  -- Вернет NULL'ы как false

-- ХОРОШО
WHERE status = 'active' OR status IS NULL;
WHERE (status != 'deleted' OR status IS NULL);
WHERE status IS NULL OR status IN ('active', 'pending');

Проверка NULL в Java

// При работе с ResultSet
String phone = rs.getString("phone");
if (phone == null) {  // resultSet возвращает Java null
    // Обработка
}

// Или использовать wasNull()
String phone = rs.getString("phone");
if (rs.wasNull()) {
    // phone был NULL в БД
}

// С объектами
Integer age = rs.getObject("age", Integer.class);
if (age != null) {
    // возраст известен
}

Заключение

Ключевые особенности NULL:

1. NULL = NULL это FALSE (используй IS NULL)
2. NULL в арифметике = NULL
3. NULL в сравнениях = UNKNOWN
4. Используй COALESCE для значения по умолчанию
5. В ORDER BY поведение отличается (MySQL vs PostgreSQL)
6. NULL в GROUP BY и DISTINCT объединяется в одну группу
7. NULL в JOIN'ах не объединяет
8. В Java всегда проверяй null при работе с БД
9. Помни о UNKNOWN (третье логическое значение)
10. Дизайн БД: подумай, действительно ли нужен NULL или лучше значение по умолчанию