Базы данных
и язык SQL

Функция COALESCE: SQL возвращает первое не-NULL значение

Навигация по уроку Связанные темы
Назад<<<ЛистатьВперёд>>>

Функция SQL COALESCE возвращает первое определённое, то есть не-NULL значение из списка её аргументов. Обычно одним или несколькими аргументами функции COALESCE является столбец таблицы, к которой обращён запрос. Нередко аргументом функции является и подзапрос. Это делается тогда, когда невозможно с уверенностью утверждать, что подзапрос вернёт какое-либо определённое значение, например, 5, "строка", '2018-12-09' и т. д., а не значение NULL. Тогда это NULL-значение будет заменено на следующее сразу же за ним определённое значение.

Приведём простые примеры пока без имён столбцов и подзапросов.

COALESCE (NULL, 7, 9) // Вернёт 7 COALESCE (NULL, 'Не найдено') // Вернёт 'Не найдено' COALESCE ('2017-10-20', NULL, '2018-03-08') // Вернёт '2018-03-08'

COALESCE для простой замены NULL-значения

При создании таблицы базы данных можно для ряда столбцов предусмотреть значения по умолчанию NULL. Тогда, если при вставке новой строки в такой столбец не вставить никакого значения, его значение будет неопределённым (NULL). Однако при выводе данных неопределённое (его ещё можно назвать пустым) значение не всегда пригодно. В таких случаях используется функция COALESCE.

В первых примерах работаем с базой данных библиотеки и её таблицей "Книга в выдаче" (BOOKINUSE). Операции будут относиться к столбцам Author (автор книги) и Title (название книги).

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными - в файле по этой ссылке.

Пример 1. Есть база данных библиотеки и таблица "Книга в выдаче" (BOOKINUSE). Таблица выглядит так:

AuthorTitlePubyearInv_NoCustomer_ID
ТолстойВойна и мир20052865
ЧеховВишневый сад20001731
ЧеховИзбранные рассказы201119120
ЧеховВишневый сад1991565
Ильф и ПетровДвенадцать стульев1985331
МаяковскийПоэмы19832120
ПастернакДоктор Живаго200669120
ТолстойВоскресенье20067747
ТолстойАнна Каренина19897205
ПушкинКапитанская дочка20042547
ГогольПьесы20078147
ЧеховИзбранные рассказы19874205
ПушкинСочинения, т.11984647
ПастернакИзбранное200013718
ПушкинСочинения, т.219848205
NULLНаука и жизнь 9 2018201912718
ЧеховРанние рассказы200117131

Как видим, в последней строке отсутствует определённное значение столбца Author, так как выданное издание является журналом. Пусть требуется вывести авторов выданных изданий с определёнными инвентарными номерами и при этом ни одно из полей не должно быть пустым. Для этого пишем запрос с использованием фукнции COALESCE:

SELECT COALESCE (Author, 'Журнал') AS InUse FROM Bookinuse WHERE inv_no IN (25, 81, 127)

Для издания с инвентарным номером 127 будет возвращено первое не-NULL значение - 'Журнал' и результирующая таблица будет выглядеть так:

InUse
Пушкин
Гоголь
Журнал

В информационных системах почти никогда не допускаются пустые строки как результат запроса. Если что-то, что было указано в запросе, отстустствует, то в результирующей строке должно быть указано 0, если речь идёт о количестве, либо "Отсутствует", если требуется текстовый ответ, либо другой подходящий по типу данных результат.

Пример 2. Вновь работаем с таблицей BOOKINUSE базы данных библиотеки. Требуется вывести количество изданий определённого автора, находящихся в выдаче. В таблице видим, что в выдаче находится одна книга Пушкина. Проверяем. Пишем следующий запрос с использованием функции COALESCE:

SELECT COALESCE ((SELECT COUNT(*) FROM Bookinuse WHERE Author='Пушкин'), 0) AS InUse

Результат выполнения этого запроса:

InUse
3

Но среди выданных изданий отстутствуют книги Булгакова. Проверяем. Пишем аналогичный запрос, меняем лишь автора:

SELECT COALESCE ((SELECT COUNT(*) FROM Bookinuse WHERE Author='Булгаков'), 0) AS InUse

Результат выполнения этого запроса:

InUse
0

Таким образом, функция COALESCE вернула первое не-NULL значение: 0 и вместо пустой строки мы получили строку со значением 0.

COALESCE для выбора альтернативы

Нередко некоторое результирующее значение основывается в зависимости от случая на значениях разных столбцов таблицы. Тогда, как правило, не участвующие в формировании результирующего значения значения столбца пусты. Для выбора необходимого столбца применяется функция COALESCE.

Пример 3. В базе данных фирмы есть таблица STAFF, по данным которой можно вычислить годовой доход сотрудника.

IDLNameSalaryCommSales
1Johnson12300NULLNULL
2BrownNULL60024
3MacGregor1420NULLNULL
4CalvinNULL78018
5Levy11400NULLNULL
6RightNULL800NULL

Если сотрудник получает фиксированную заработную плату (Salary), то значения столбцов Комиссионные (Comm) и Сделки (Sales) пусты (NULL). В таком случае для получения годового дохода следует размер заработной платы умножить на 12. Если же сотрудик получает комиссионные, то значение столбца Salary пусто (NULL). Возможны также случаи, когда сотруднику назначены комиссионные, но он не провёл ни одной сделки. Тогда значение столбца Sales пусто (NULL). В первом случае функция COALESCE возвращает значение Salary*12, во втором - Comm*Sales, в третьем - 0. Итак, для вычисления годового дохода сотрудников пишем следующий запрос с использованием функции COALESCE:

SELECT LName, COALESCE (Salary*12, Comm*Sales, 0) AS Income FROM STAFF

Результатом выполнения запроса будет следующая таблица:

LNameIncome
Johnson147600
Brown14400
MacGregor170400
Calvin14040
Levy136800
Right0

COALESCE помогает избежать неопределённости в вычислениях

В соединениях таблиц часто невозможно заранее предположить, всем ли значениям некоторого столбца из одной таблице соответствует определённое значение из другой таблице. В случае несоответствия значение является неопределённым (NULL). Но именно на основании этого значения должны производиться дополнительные вычисления. Другая причина, по которой в сложных вычислениях часто применяется функция COALESCE, состоит в том, что запрещается применять агрегатные функции от агрегатной функции, например, SUM(COUNT(*).

Работаем с базой данных "Театр". Таблица Play содержит данные о постановках. Таблица Team - о ролях актёров. Таблица Actor - об актёрах. Таблица Director - о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 4. В таблице Team есть столбец MainTeam, содержащий данные о том, является ли роль главной. Если является, то значение столбца - Y, если нет - N. Требуется вывести список актёров с фамилиями и количеством второстепенных ролей.

Потребуется соединение таблиц. Как мы уже замечали, в соединении таблиц Play (постановка) и Team (роль) некоторые значения столбцов могут быть неопределёнными из-за того, что не у всех актёров в каждой постановке обязательно есть и главые, и второстепенные роли. Кроме того, в качестве количества второстепенных ролей требуется подсчитать сумму (SUM) числа строк (COUNT(*)), соответствующих определённому актёру, в которых указано, что роль является второстепеннной. Но использование вложенных агрегатных функций запрещено. В этом случае пишется запрос с применением функции COALESCE, возвращаемое которой значение уже формально не является значением агрегатной функции:

SELECT a.LName AS Name, SUM (COALESCE((SELECT COUNT(*) FROM ACTOR a1 JOIN team t ON a1.Actor_ID-t.ACTOR_ID WHERE a1.Actor_ID=a.Actor_ID AND t.MainTeam='N' GROUP BY a1.Actor_ID), 0)) AS NumSecRole FROM ACTOR a JOIN team t ON a.Actor_ID=t.ACTOR_ID JOIN Play p ON t.PLAY_ID=p.Play_ID ORDER BY a.Actor_ID

Поделиться с друзьями

Назад<<<СодержаниеВперёд>>>