Оптимизация функции COALESCE для NULL и пустых строк

Быстрый ответ

Для того чтобы преобразовать NULL и пустые строки в нечто смысловое, следует сначала заменить пустые строки на NULL, используя NULLIF функцию, а затем применить COALESCE функцию со значением по умолчанию:

-- Обрабатывает и "" и NULL, подставляя 'fallback'
SELECT COALESCE(NULLIF(column, ''), 'fallback') FROM table;

Если речь идет о нескольких столбцах, следует применить NULLIF внутри COALESCE для каждого из них:

-- Возвращает первое непустое значение из списка или 'fallback'
SELECT COALESCE(NULLIF(col1, ''), NULLIF(col2, ''), 'fallback') FROM table;

Как улучшить производительность

Иногда можно встретить мнение о том, что пользовательские функции работают медленно, в отличие от встроенных SQL-функций, которые действуют неуклонно быстро. Однако бывает, что хорошо оптимизированные пользовательские функции могут работать со сравнимой скоростью.

Визуализация

Попробуем представить этот процесс на примере коробки карандашей (🖍️). Какие-то ячейки могут оказаться пустыми (символизируя NULL значения), а в других могут находиться карандаши, которые не оставляют никаких следов на бумаге (пустые строки):

| Ячейка в Коробке | Карандаш             | Цвет Рисунка   |
| ---------------- | -------------------- | -------------- |
| 1                | Красный Карандаш     | 🟥             |
| 2                | NULL (отсутствует)   |                |
| 3                | "" (прозрачный)      |                |
| 4                | Синий Карандаш       | 🟦             |

Применяя функцию COALESCE, мы получаем:

-- Заменяем 'NULL' и прозрачные карандаши на 'Default Color'
COALESCE(Crayon, 'Default Color')

А вот и результат после применения COALESCE:

| Ячейка в Коробке | Карандаш                       | Цвет Рисунка     |
| ---------------- | ------------------------------ | ---------------- |
| 1                | Красный Карандаш               | 🟥               |
| 2                | 'Цвет По Умолчанию'            | 🟨 (Заполнено)   |
| 3                | 'Цвет По Умолчанию'            | 🟨 (Заполнено)   |
| 4                | Синий Карандаш                 | 🟦               |

Таким образом, при применении COALESCE мы заменили и невидимые карандаши (""), и пустые ячейки (NULL) на цвет, видимый глазу ('Цвет По Умолчанию' 🟨).

Углубляемся в детали

Применяем COALESCE и NULLIF вместе

Комбинирование NULLIF(column_name, '') и COALESCE эффективно борется с незначащими NULL и пустыми строками:

-- Первый значащий элемент становится результатом. Если таковых нет — возвращается 'none'.
SELECT COALESCE(NULLIF(col1, ''), NULLIF(col2, ''), 'none') FROM table;

Подробнее об этом расскажет наш спикер на видео

Делаем функции быстрыми и эффективными

Для обработки нескольких столбцов при сохранении скорости исполнения вы можете создать пользовательские скалярные функции:

-- Быстрая и оснащенная пользовательскими возможностями функция
CREATE FUNCTION dbo.OptimizedCoalesce(@col1 VARCHAR(MAX), @col2 VARCHAR(MAX), ...)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN COALESCE(NULLIF(@col1, ''), NULLIF(@col2, ''), ...,'fallback')
END;

Помните об учете времени выполнения и сравнении его с работой встроенных функций.

Обеспечиваем масштабируемость

Если вам нужно работать с списками произвольной длины, будет разумнее использовать табличные функции или динамический SQL.

Учитываем особые случаи

Бывают моменты, когда даже пустые строки имеют значение. Заблаговременно подумайте, не потребуется ли в таких ситуациях особый подход.

Полезные материалы

1. Функция COALESCE() в SQL Server — Подробное руководство по использованию COALESCE в SQL.
2. Многообразие способов применения COALESCE в SQL Server — Описывает различные применения COALESCE.
3. Какие инструменты и технологии используются для создания Сети Stack Exchange? — Взгляд под капот StackOverflow.
4. Функция COALESCE в SQL Server — Введение в практическое использование COALESCE.
5. Замена NULL на пустое значение в SQL Server — Раздел рассказывает, как функция COALESCE работает с NULL и пустыми строками.