NVL, COALESCE и NVL2: различия и практические кейсы

Друзья, всем привет! 👋

Сегодня разберём три функции для работы с NULL, которые часто путают начинающие разработчики: NVL, NVL2 и COALESCE.

На первый взгляд все они решают одну задачу — заменяют NULL на что-то осмысленное. Но каждая имеет свои особенности, которые влияют на производительность и результат выполнения запроса.

NVL (Null Value Logic)

Самая простая и древняя функция Oracle для замены NULL.

Синтаксис:

NVL(выражение, значение_если_NULL)

🔹 Принимает ровно 2 аргумента
🔹 Если первый аргумент NULL — возвращает второй
🔹 Вычисляет оба аргумента (кроме констант - Oracle их оптимизирует)

Пример:

SELECT employee_name, NVL(commission_pct, 0) AS commission
FROM employees;

COALESCE (Стандарт SQL)

Более гибкая функция, которая может принимать любое количество аргументов.

Синтаксис:

COALESCE(выражение1, выражение2, ..., выражениеN)

🔹 Принимает 2 и более аргументов
🔹 Возвращает первое найденное не-NULL значение из списка
🔹 Вычисляет аргументы последовательно и останавливается на первом не-NULL (более эффективно!)

Пример:

SELECT employee_name,
    COALESCE(mobile_phone, work_phone, home_phone, 'No phone') AS contact
FROM employees;

NVL2 (расширенная версия NVL)

Уникальная функция Oracle для разных значений в NULL и не-NULL случаях.

Синтаксис:

NVL2(выражение, значение_если_НЕ_NULL, значение_если_NULL)

🔹 Принимает ровно 3 аргумента
🔹 Если первый аргумент НЕ NULL — возвращает второй
🔹 Если первый аргумент NULL — возвращает третий

Пример:

SELECT employee_name,
    NVL2(commission_pct, 
         salary + (salary * commission_pct), 
         salary) AS total_compensation
FROM employees;

Практический кейс: производительность

Важный момент! NVL всегда вычисляет оба аргумента (кроме констант), а COALESCE — только до первого не-NULL.

-- NVL вызовет expensive_function() ВСЕГДА
SELECT NVL(column_value, expensive_function()) FROM table_name;

-- COALESCE вызовет только если column_value IS NULL
SELECT COALESCE(column_value, expensive_function()) FROM table_name;

Если у вас миллионы строк и дорогая функция — разница будет колоссальной! 🚀

Практический кейс: множественные проверки

Найти первый доступный контакт клиента:

-- С NVL — громоздко
SELECT NVL(NVL(NVL(email, mobile), work_phone), 'No contact')
FROM customers;

-- С COALESCE — элегантно
SELECT COALESCE(email, mobile, work_phone, 'No contact')
FROM customers;

Практический кейс: условная логика с NVL2

Расчёт бонуса по разным формулам:

-- Без NVL2 — через CASE
SELECT employee_name,
    CASE WHEN commission_pct IS NOT NULL 
         THEN salary * 0.1 
         ELSE salary * 0.05 
    END AS bonus
FROM employees;

-- С NVL2 — компактнее
SELECT employee_name,
    NVL2(commission_pct, salary * 0.1, salary * 0.05) AS bonus
FROM employees;

Ловушка с типами данных

-- Ошибка! Несовместимые типы
SELECT NVL(salary, 'Unknown') FROM employees;

-- Правильно — приводим к одному типу
SELECT NVL(TO_CHAR(salary), 'Unknown') FROM employees;

-- Oracle обрабатывает пустую строку как NULL!
SELECT NVL('', 'Default') FROM dual; -- Вернет 'Default'

Обратите внимание: NVL всегда возвращает тип первого аргумента, а COALESCE — может выбрать более общий тип (например, NUMBER + VARCHAR2 даст VARCHAR2).

Когда что использовать?

✅ NVL — для простой замены NULL на значение по умолчанию (Oracle)

✅ COALESCE — когда нужно проверить несколько значений или важна производительность (современный подход!). Стандарт SQL — работает везде!

✅ NVL2 — когда нужна условная логика на основе NULL/не-NULL (Oracle)

Вывод

Все три функции решают проблему NULL, но по-разному:
• NVL — простая, но ограниченная
• COALESCE — гибкая и эффективная
• NVL2 — для условных сценариев

Какую функцию вы используете чаще? Делитесь опытом в чате! 💬

Всем продуктивной разработки и минимум NULL-проблем! 🚀

#oracle #nvl #coalesce #nvl2 #sql #null #базыданных #разработка #plsql

Канал Oracle Developer | Чатик 💬
Мини-курс Оптимизация: Быстрый старт 🚀

Анкета предзаписи на 7 поток "Оптимизация Oracle SQL" 🔥

📱 Facebook 📱 YouTube 📱 ВКонтакте 📱 LinkedIn 📱 Threads RUTUBE