Функция COALESCE: SQL возвращает первое не-NULL значение
| Назад<<< | Листать | Вперёд>>> |
Функция SQL COALESCE возвращает первое определённое, то есть не-NULL значение из списка её аргументов. Обычно одним или несколькими аргументами функции COALESCE является столбец таблицы, к которой обращён запрос. Нередко аргументом функции является и подзапрос. Это делается тогда, когда невозможно с уверенностью утверждать, что подзапрос вернёт какое-либо определённое значение, например, 5, "строка", '2018-12-09' и т. д., а не значение NULL. Тогда это NULL-значение будет заменено на следующее сразу же за ним определённое значение.
Приведём простые примеры пока без имён столбцов и подзапросов.
COALESCE для простой замены NULL-значения
При создании таблицы базы данных можно для ряда столбцов предусмотреть значения по умолчанию NULL. Тогда, если при вставке новой строки в такой столбец не вставить никакого значения, его значение будет неопределённым (NULL). Однако при выводе данных неопределённое (его ещё можно назвать пустым) значение не всегда пригодно. В таких случаях используется функция COALESCE.
В первых примерах работаем с базой данных библиотеки и её таблицей "Книга в выдаче" (BOOKINUSE). Операции будут относиться к столбцам Author (автор книги) и Title (название книги).
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.
Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными - в файле по этой ссылке.
Пример 1. Есть база данных библиотеки и таблица "Книга в выдаче" (BOOKINUSE). Таблица выглядит так:
| Author | Title | Pubyear | Inv_No | Customer_ID |
| Толстой | Война и мир | 2005 | 28 | 65 |
| Чехов | Вишневый сад | 2000 | 17 | 31 |
| Чехов | Избранные рассказы | 2011 | 19 | 120 |
| Чехов | Вишневый сад | 1991 | 5 | 65 |
| Ильф и Петров | Двенадцать стульев | 1985 | 3 | 31 |
| Маяковский | Поэмы | 1983 | 2 | 120 |
| Пастернак | Доктор Живаго | 2006 | 69 | 120 |
| Толстой | Воскресенье | 2006 | 77 | 47 |
| Толстой | Анна Каренина | 1989 | 7 | 205 |
| Пушкин | Капитанская дочка | 2004 | 25 | 47 |
| Гоголь | Пьесы | 2007 | 81 | 47 |
| Чехов | Избранные рассказы | 1987 | 4 | 205 |
| Пушкин | Сочинения, т.1 | 1984 | 6 | 47 |
| Пастернак | Избранное | 2000 | 137 | 18 |
| Пушкин | Сочинения, т.2 | 1984 | 8 | 205 |
| NULL | Наука и жизнь 9 2018 | 2019 | 127 | 18 |
| Чехов | Ранние рассказы | 2001 | 171 | 31 |
Как видим, в последней строке отсутствует определённное значение столбца Author, так как выданное издание является журналом. Пусть требуется вывести авторов выданных изданий с определёнными инвентарными номерами и при этом ни одно из полей не должно быть пустым. Для этого пишем запрос с использованием фукнции COALESCE:
Для издания с инвентарным номером 127 будет возвращено первое не-NULL значение - 'Журнал' и результирующая таблица будет выглядеть так:
| InUse |
| Пушкин |
| Гоголь |
| Журнал |
В информационных системах почти никогда не допускаются пустые строки как результат запроса. Если что-то, что было указано в запросе, отстустствует, то в результирующей строке должно быть указано 0, если речь идёт о количестве, либо "Отсутствует", если требуется текстовый ответ, либо другой подходящий по типу данных результат.
Пример 2. Вновь работаем с таблицей BOOKINUSE базы данных библиотеки. Требуется вывести количество изданий определённого автора, находящихся в выдаче. В таблице видим, что в выдаче находится одна книга Пушкина. Проверяем. Пишем следующий запрос с использованием функции COALESCE:
Результат выполнения этого запроса:
| InUse |
| 3 |
Но среди выданных изданий отстутствуют книги Булгакова. Проверяем. Пишем аналогичный запрос, меняем лишь автора:
Результат выполнения этого запроса:
| InUse |
| 0 |
Таким образом, функция COALESCE вернула первое не-NULL значение: 0 и вместо пустой строки мы получили строку со значением 0.
COALESCE для выбора альтернативы
Нередко некоторое результирующее значение основывается в зависимости от случая на значениях разных столбцов таблицы. Тогда, как правило, не участвующие в формировании результирующего значения значения столбца пусты. Для выбора необходимого столбца применяется функция COALESCE.
Пример 3. В базе данных фирмы есть таблица STAFF, по данным которой можно вычислить годовой доход сотрудника.
| ID | LName | Salary | Comm | Sales |
| 1 | Johnson | 12300 | NULL | NULL |
| 2 | Brown | NULL | 600 | 24 |
| 3 | MacGregor | 1420 | NULL | NULL |
| 4 | Calvin | NULL | 780 | 18 |
| 5 | Levy | 11400 | NULL | NULL |
| 6 | Right | NULL | 800 | NULL |
Если сотрудник получает фиксированную заработную плату (Salary), то значения столбцов Комиссионные (Comm) и Сделки (Sales) пусты (NULL). В таком случае для получения годового дохода следует размер заработной платы умножить на 12. Если же сотрудик получает комиссионные, то значение столбца Salary пусто (NULL). Возможны также случаи, когда сотруднику назначены комиссионные, но он не провёл ни одной сделки. Тогда значение столбца Sales пусто (NULL). В первом случае функция COALESCE возвращает значение Salary*12, во втором - Comm*Sales, в третьем - 0. Итак, для вычисления годового дохода сотрудников пишем следующий запрос с использованием функции COALESCE:
Результатом выполнения запроса будет следующая таблица:
| LName | Income |
| Johnson | 147600 |
| Brown | 14400 |
| MacGregor | 170400 |
| Calvin | 14040 |
| Levy | 136800 |
| Right | 0 |
COALESCE помогает избежать неопределённости в вычислениях
В соединениях таблиц часто невозможно заранее предположить, всем ли значениям некоторого столбца из одной таблице соответствует определённое значение из другой таблице. В случае несоответствия значение является неопределённым (NULL). Но именно на основании этого значения должны производиться дополнительные вычисления. Другая причина, по которой в сложных вычислениях часто применяется функция COALESCE, состоит в том, что запрещается применять агрегатные функции от агрегатной функции, например, SUM(COUNT(*).
Работаем с базой данных "Театр". Таблица Play содержит данные о постановках. Таблица Team - о ролях актёров. Таблица Actor - об актёрах. Таблица Director - о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).
Пример 4. В таблице Team есть столбец MainTeam, содержащий данные о том, является ли роль главной. Если является, то значение столбца - Y, если нет - N. Требуется вывести список актёров с фамилиями и количеством второстепенных ролей.
Потребуется соединение таблиц. Как мы уже замечали, в соединении таблиц Play (постановка) и Team (роль) некоторые значения столбцов могут быть неопределёнными из-за того, что не у всех актёров в каждой постановке обязательно есть и главые, и второстепенные роли. Кроме того, в качестве количества второстепенных ролей требуется подсчитать сумму (SUM) числа строк (COUNT(*)), соответствующих определённому актёру, в которых указано, что роль является второстепеннной. Но использование вложенных агрегатных функций запрещено. В этом случае пишется запрос с применением функции COALESCE, возвращаемое которой значение уже формально не является значением агрегатной функции:
Поделиться с друзьями
| Назад<<< | Содержание | Вперёд>>> |
