SQL возвращает первое не-NULL значение
Оглавление
- COALESCE для простой замены NULL-значения
- COALESCE для выбора альтернативы
- COALESCE помогает избежать неопределённости в вычислениях
Связанные темы
- Оператор SELECT
- Подзапросы
| Назад | Листать | Вперёд>>> |
Функция SQL COALESCE возвращает первое определённое, то есть не-NULL значение из списка её аргументов. Обычно
одним или несколькими аргументами функции COALESCE является столбец таблицы, к которой обращён запрос. Нередко
аргументом функции является и подзапрос. Это делается тогда, когда невозможно
с уверенностью утверждать, что подзапрос вернёт какое-либо определённое значение, например, 5, «строка», ‘2018-12-09’ и
т. д., а не значение NULL.
Приведём простые примеры пока без имён столбцов и подзапросов.
COALESCE (NULL, 7, 9) // Вернёт 7 COALESCE (NULL, ‘Не найдено’) // Вернёт ‘Не найдено’ COALESCE (‘2017-10-20’, NULL, ‘2018-03-08’) // Вернёт ‘2018-03-08’
При создании таблицы базы данных можно для ряда столбцов предусмотреть значения по умолчанию NULL. Тогда, если при вставке новой строки в такой столбец не вставить никакого значения, его значение будет неопределённым (NULL). Однако при выводе данных неопределённое (его ещё можно назвать пустым) значение не всегда пригодно. В таких случаях используется функция COALESCE.
В первых примерах работаем с базой данных библиотеки и её таблицей «Книга в выдаче» (BOOKINUSE). Операции будут относиться к столбцам Author (автор книги) и Title (название книги).
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД
не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.
Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными — в файле по этой ссылке.
| Author | Title | Pubyear | Inv_No | Customer_ID |
| Толстой | Война и мир | 2005 | 28 | 65 |
| Чехов | Вишневый сад | 2000 | 17 | 31 |
| Чехов | Избранные рассказы | 2011 | 19 | 120 |
| Чехов | Вишневый сад | 1991 | 5 | 65 |
| Ильф и Петров | Двенадцать стульев | 1985 | 3 | 31 |
| Маяковский | Поэмы | 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:
SELECT COALESCE (Author, ‘Журнал’) AS InUse FROM Bookinuse WHERE inv_no IN (25, 81, 127)
Для издания с инвентарным номером 127 будет возвращено первое не-NULL значение — ‘Журнал’ и результирующая таблица будет выглядеть так:
| InUse |
| Пушкин |
| Гоголь |
| Журнал |
- Страница 2 (функция COALESCE в более сложных запросах)
В информационных системах почти никогда не допускаются пустые строки как результат запроса.
Если что-то,
что было указано в запросе, отстустствует, то в результирующей строке должно быть указано 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.
- Страница 2 (функция 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:
SELECT LName, COALESCE (Salary*12, Comm*Sales, 0) AS Income FROM STAFF
Результатом выполнения запроса будет следующая таблица:
| LName | Income |
| Johnson | 147600 |
| Brown | 14400 |
| MacGregor | 170400 |
| Calvin | 14040 |
| Levy | 136800 |
| Right | 0 |
В соединениях таблиц часто невозможно заранее предположить, всем ли значениям некоторого столбца из
одной таблице соответствует определённое значение из другой таблице.
В случае несоответствия значение
является неопределённым (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
- Страница 2 (функция COALESCE в более сложных запросах)
Поделиться с друзьями
| Назад | Листать | Вперёд>>> |
Преобразование значения NULL в MySQL с помощью COALESCE() — средний репост
Помнить о значениях NULL — хорошая практика.
NULL s могут ускользнуть от вас и дать вам чертовски много времени (особенно в вычислениях), если вы не будете осторожны . В этом посте мы увидим примеры преобразования NULL с функцией MySQL COALESCE() .
Ознакомьтесь со статьями премиум-класса, подробными руководствами, обучающими видеороликами и многим другим, присоединившись к «Уровень обучения MySQL» членство. У вас есть доступ к эксклюзивному контенту, неопубликованному где-либо еще с этим членством. Поскольку новый контент регулярно добавляется, продолжайте изучать MySQL на любом уровне.
Изучайте MySQL!
Изображение Герда Альтманна из Pixabay
Информационный бюллетень для разработчиков PHP и MySQL
Получите копию моей электронной книги «10 советов по MySQL для всех» , абсолютно бесплатно при подписке на Информационный бюллетень OpenLampTech .
MySQL Функция COALESCE() принимает несколько аргументов и возвращает первое значение, отличное от NULL . Если нет не- NULL s, COALESCE() возвращает NULL .
Получайте индивидуальные статьи с Refind каждый день в свой почтовый ящик. Refind — часть моей ежедневной привычки к чтению. Сделайте его частью своего, используя мою реферальную ссылку. Без каких-либо дополнительных затрат для вас я буду иметь право на премиум-подписку с большим количеством регистраций по моей ссылке. “ Суть Интернета, каждое утро в вашем почтовом ящике. Подпишитесь бесплатно »
Прочтите весь пост, Преобразуйте значения NULL с помощью функции MySQL COALESCE, чтобы узнать, как заменить значения NULL чем-то более значимым в результатах запроса.
Самореклама:
Если вам нравится содержание, написанное здесь, во что бы то ни стало, поделитесь этим блогом и вашими любимыми сообщениями с другими, кому они тоже могут быть полезны или понравиться.
Поскольку кофе — мой любимый напиток, вы даже можете купить мне его, если хотите!
Продемонстрируйте свою поддержку моего блога и контента, бросив любую мелочь, которая у вас есть, в мою банку для чаевых.
Баночка для чаевых
Вы являетесь пользователем Medium? Если это так, получайте уведомление по электронной почте каждый раз, когда я публикую сообщение в блоге, если вы предпочитаете платформу Medium. Не член? Без проблем! Воспользуйтесь моей ссылкой для регистрации (я получу комиссию без дополнительных затрат для вас) и присоединяйтесь. Мне очень нравится читать весь этот замечательный контент, и я знаю, что вам тоже понравится!!!
Аналогичное чтение
Если вам понравилась эта статья, вам также может понравиться любой из этих постов. Поделитесь ими вместе. Спасибо 🙏
- MySQL IFNULL()
- SUBSTRING_INDEX()
- Recursive CTE Fun in MySQL
- MySQL SHOW TABLES
Узнайте о моих личных целях в изучении бэкэнд-веб-разработки, и я разделяю мои личные цели в изучении веб-разработки кодировать.
Прочтите это интервью.
Спасибо, что прочитали этот пост. Пожалуйста, поделитесь им с кем-то еще, кому это тоже понравится.
Джош Отвелл хочет развиваться как разработчик PHP, эксперт по SQL и технический блоггер/писатель.
Отказ от ответственности: большинство примеров в этом посте выполняются в среде рабочей станции для персональной разработки/обучения, и не следует считать готовым к производству или готовым . Ваши конкретные цели и потребности могут отличаться. Как всегда, только потому, что вы можете что-то сделать, не означает, что вы должны . Мои мнения являются моими собственными.
Чем еще я могу помочь
- Создаете блог? Я использую WordPress для блога Digital Owl’s Prose . Давайте вместе сэкономим деньги на предложенных планах. 💸
- Привлеките к своему бренду, продукту или услуге то внимание, которого они заслуживают, разместив рекламу по доступной цене в информационном бюллетене OpenLampTech .
- Нужен хостинг для вашего следующего веб-приложения или сайта WordPress? Я настоятельно рекомендую Hostinger и использую их для размещения своего нишевого сайта о ловле окуня. Сервис не имеет себе равных, и они предоставляют бесплатный SSL.
- 🔒5 истин, которые я осознал как разработчик-самоучка
Раскрытие информации : Некоторые из ссылок на услуги и продукты в этом посте являются партнерскими ссылками. Без каких-либо дополнительных затрат для вас, если вы совершите покупку, нажав на один из них, я получу комиссию.
Информационный бюллетень для разработчиков PHP и MySQL
Получите копию моей электронной книги «10 советов по MySQL для всех» , абсолютно бесплатно при подписке на информационный бюллетень OpenLampTech .
Информационный бюллетень для разработчиков PHP и MySQL
Получите копию моей электронной книги «10 советов по MySQL для всех» , абсолютно бесплатно при подписке на информационный бюллетень OpenLampTech .
Раскрытие информации : Некоторые ссылки на услуги и продукты в этом посте являются партнерскими ссылками. Без каких-либо дополнительных затрат для вас, если вы совершите покупку, нажав на один из них, я получу комиссию.
Нравится:
Нравится Загрузка…
Дата объединения MySQL отсутствует, почему?
Не используйте МЕЖДУ для проверки даты или диапазона даты и времени; это включительно , поэтому вы получаете секунды (обычно полночь) на обоих концах.
Для проверки DATE или DATETIME на один «день» сделайте следующее:
WHERE col >= '2019-12-19' И столбец < '2019-12-19' + ИНТЕРВАЛ 1 ДЕНЬ
Это будет работать "правильно", независимо от того, является ли столбец DATE, DATETIME или DATETIME(6). И оптимизатор в целом доволен этим выражением.
Также имейте в виду, что CURDATE() — полночь сегодняшнего утра.
(Опровержение ypercube 🙂
Возможно, это демонстрирует, что проблема связана с типом данных, возвращаемым COALESCE , а не с существованием 00:00:00 :
07.2016» и «31.07.2016»;
Пустой набор (0,00 сек)
mysql> ВЫБЕРИТЕ dt, just_date ИЗ dtts, ГДЕ ОБЪЕДИНИТЬ (dt, DATE ('9999-1-1'))
между «31.07.2016» и «31.07.2016»;
+---------------------+-------------+
| дт | только_дата |
+---------------------+-------------+
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
+---------------------+-------------+
5 рядов в сете (0,00 сек)
mysql> ВЫБЕРИТЕ dt, just_date ОТ dtts WHERE COALESCE(just_date, '9999-1-1')
между «31.07.2016» и «31.07.2016»;
+---------------------+-------------+
| дт | только_дата |
+---------------------+-------------+
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
+---------------------+-------------+
mysql> ВЫБЕРИТЕ dt, just_date ОТ DTTS, ГДЕ ОБЪЕДИНИТЬ (just_date, '9999-1-1')
между «2016-07-31 00:00:00» и «2016-07-31 00:00:00»;
Пустой набор (0,00 сек) Coalesce, похоже, возвращает DATE или DATETIME:
mysql> SELECT dt, COALESCE(dt, DATE('9999-1-1')), just_date, COALESCE(just_date, DATE('9999-1-1')) из дтц;
+------------------------------------+-------------- -----+------------+-------------------------------- --------+
| дт | ОБЪЕДИНЕНИЕ(дт, ДАТА('9999-1-1')) | только_дата | COALESCE(just_date, DATE('9999-1-1')) |
+------------------------------------+-------------- -----+------------+-------------------------------- --------+
| 2011-06-08 20:45:55 | 2011-06-08 20:45:55 | 2011-06-08 | 2011-06-08 |
| 2013-03-10 02:35:47 | 2013-03-10 02:35:47 | 2013-03-10 | 2013-03-10 |
| 2014-02-08 09:36:48 | 08.