COALESCE в Oracle SQL — первое не NULL значение

COALESCE в Oracle SQL. Введение

Функция COALESCE в Oracle SQL позволяет возвращать первое не NULL значение из списка выражений. Это мощная альтернатива для NVL и CASE, особенно когда нужно проверить сразу несколько значений. В этой статье ты найдёшь 45 практических примеров использования COALESCE для чисел, строк, дат и выражений.

🖋️ Синтаксис функции COALESCE

COALESCE(expr1, expr2, ..., exprN)

• Возвращает первое НЕ NULL значение из переданных.
• Если все значения NULL — вернёт NULL.
• Тип результата определяется по первому не NULL значению.

🔄 Где используется COALESCE

• Обработка значений по приоритету (первый доступный)
• Альтернатива NVL / NVL2
• Работа со строками, датами, числовыми значениями
• Вывод по умолчанию в SELECT, UPDATE, WHERE

📊 Примеры использования COALESCE в Oracle SQL (50 примеров)

1. COALESCE с двумя колонками:

SELECT first_name, COALESCE(commission_pct, bonus) FROM employees;

2. COALESCE и значение по умолчанию:

SELECT COALESCE(commission_pct, 0.05) FROM employees;

3. COALESCE в WHERE:

SELECT * FROM employees
WHERE COALESCE(commission_pct, 0) > 0.03;

4. COALESCE со строками:

SELECT employee_id, COALESCE(nickname, first_name, 'Без имени') FROM users;

5. COALESCE в UPDATE:

UPDATE employees SET bonus = COALESCE(commission_pct, 0.1);

6. COALESCE с NULL значениями:

SELECT COALESCE(NULL, NULL, 'резерв') FROM dual;

7. COALESCE в SELECT с числами:

SELECT employee_id, COALESCE(bonus, salary * 0.1) AS выплата FROM employees;

8. COALESCE и NVL сравнение:

SELECT NVL(commission_pct, 0), COALESCE(commission_pct, 0) FROM employees;

9. COALESCE в INSERT:

INSERT INTO logs (message) VALUES (COALESCE(:input_text, 'по умолчанию'));

10. COALESCE и CASE:

SELECT employee_id,
  CASE
    WHEN COALESCE(commission_pct, 0) > 0 THEN 'Бонус есть'
    ELSE 'Нет бонуса'
  END
FROM employees;

11. COALESCE в аналитике:

SELECT employee_id, RANK() OVER (ORDER BY COALESCE(bonus, 0) DESC) AS рейтинг FROM employees;

12. COALESCE и TO_CHAR:

SELECT COALESCE(TO_CHAR(bonus), 'нет данных') FROM employees;

13. COALESCE и TO_DATE:

SELECT COALESCE(start_date, TO_DATE('2023-01-01','YYYY-MM-DD')) FROM job_history;

14. COALESCE и TO_NUMBER:

SELECT COALESCE(TO_NUMBER(NULL), 100) FROM dual;

15. COALESCE с JSON_VALUE:

SELECT COALESCE(JSON_VALUE(data, '$.email'), 'не указано') FROM employees_json;

16. COALESCE с датами:

SELECT employee_id, COALESCE(termination_date, SYSDATE) AS актуальная_дата FROM employees;

17. COALESCE в MERGE:

MERGE INTO employees e USING dual d
ON (e.employee_id IS NOT NULL)
WHEN MATCHED THEN UPDATE SET bonus = COALESCE(e.bonus, 0.05);

18. COALESCE с ROWNUM:

SELECT employee_id, COALESCE(bonus, 0) FROM employees WHERE ROWNUM <= 5;

19. COALESCE в GROUP BY:

SELECT COALESCE(department_id, 0) AS отдел, COUNT(*) FROM employees GROUP BY COALESCE(department_id, 0);

20. COALESCE и ORDER BY:

SELECT * FROM employees ORDER BY COALESCE(bonus, 0) DESC;

21. COALESCE и INLINE VIEW:

SELECT * FROM (
  SELECT employee_id, COALESCE(bonus, 0) AS бонус FROM employees
) t WHERE бонус > 0.1;

22. COALESCE с подзапросом:

SELECT employee_id, COALESCE((SELECT bonus FROM bonuses WHERE emp_id = e.employee_id), 0)
FROM employees e;

23. COALESCE с текстом и числами:

SELECT COALESCE(NULL, 'текст', TO_CHAR(100)) FROM dual;

24. COALESCE и регулярные выражения:

SELECT COALESCE(REGEXP_SUBSTR(email, '\w+@\w+\.com'), 'невалидно') FROM employees;

25. COALESCE и NVL2:

SELECT NVL2(bonus, bonus, salary * 0.05), COALESCE(bonus, salary * 0.05) FROM employees;

26. COALESCE в представлении:

CREATE OR REPLACE VIEW v_bonus_default AS
SELECT employee_id, COALESCE(bonus, 0.1) AS bonus FROM employees;

27. COALESCE с INITCAP:

SELECT COALESCE(INITCAP(nickname), 'Аноним') FROM users;

28. COALESCE и EXTRACT:

SELECT employee_id, EXTRACT(YEAR FROM COALESCE(termination_date, SYSDATE)) AS год FROM employees;

29. COALESCE в DELETE:

DELETE FROM employees WHERE COALESCE(bonus, 0) = 0;

30. COALESCE в JOIN:

SELECT e.*, COALESCE(p.salary_bonus, 0) FROM employees e
LEFT JOIN payroll p ON e.employee_id = p.emp_id;

31. COALESCE с TO_TIMESTAMP:

SELECT COALESCE(event_time, TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) FROM logs;

32. COALESCE и LENGTH:

SELECT LENGTH(COALESCE(email, 'пусто')) FROM employees;

33. COALESCE в JSON_TABLE:

SELECT * FROM JSON_TABLE(
  '{"users": [{"name": null}]}'
  , '$.users[*]'
  COLUMNS (name VARCHAR2(50) PATH '$.name' DEFAULT 'гость' ON ERROR)
);

34. COALESCE в аналитике NTILE:

SELECT employee_id, NTILE(3) OVER (ORDER BY COALESCE(bonus, 0)) FROM employees;

35. COALESCE с SIGN:

SELECT employee_id,
  SIGN(COALESCE(bonus, 0)) AS знак FROM employees;

36. COALESCE в UPDATE с CASE:

UPDATE employees SET status =
  CASE
    WHEN COALESCE(bonus, 0) > 0 THEN 'Активен'
    ELSE 'Обычный'
  END;

37. COALESCE и SUM:

SELECT department_id, SUM(COALESCE(bonus, 0)) AS общий_бонус FROM employees GROUP BY department_id;

38. COALESCE и AVG:

SELECT AVG(COALESCE(commission_pct, 0)) FROM employees;

39. COALESCE и LISTAGG:

SELECT LISTAGG(COALESCE(first_name, 'без имени'), ', ') WITHIN GROUP (ORDER BY employee_id) FROM employees;

40. COALESCE с TRIM:

SELECT TRIM(COALESCE(comment_text, '')) FROM feedbacks;

41. COALESCE в аналитике RANK:

SELECT employee_id, RANK() OVER (ORDER BY COALESCE(bonus, 0)) AS позиция FROM employees;

42. COALESCE в PIVOT:

SELECT * FROM (
  SELECT department_id, COALESCE(bonus, 0) AS бонус FROM employees
)
PIVOT (COUNT(*) FOR бонус IN (0, 0.1, 0.2));

43. COALESCE и TO_CLOB:

SELECT TO_CLOB(COALESCE(comment_text, 'нет')) FROM feedbacks;

44. COALESCE с CLOB и TEXT:

SELECT COALESCE(to_clob(notes), 'без примечаний') FROM reports;

45. COALESCE и UNION ALL:

SELECT employee_id, COALESCE(bonus, 0) AS бонус FROM employees
UNION ALL
SELECT emp_id, 0.05 FROM temp_bonus;

—

📆 Заключение или зачем использовать COALESCE в Oracle SQL

Функция COALESCE делает SQL-код короче, понятнее и безопаснее. Она экономит строки кода, избавляет от громоздких CASE и проверок на NULL. Особенно полезна, когда нужно вернуть значение «по приоритету»: сначала одно, если нет — второе, и так далее. COALESCE отлично подходит для обработки NULL, построения отчётов, подстановок и резервных значений в подзапросах и агрегации.

❓ В каких ситуациях тебе помог COALESCE? Поделись в комментариях!

📚 Официальная документация по ключевому слову COALESCE доступна на сайте Oracle: Oracle SQL Language Reference: COALESCE

🕸️ Следующая статья:

CAST в Oracle SQL — как преобразовывать типы данных