Выборка только ненулевых значений в MySQL: без PHP
Чтобы выбрать записи, в которых не встречаются значения NULL , используйте в соответствии с WHERE условие IS NOT NULL :
Скопировать код
SELECT * FROM your_table WHERE your_column IS NOT NULL;Этот запрос вернёт только те строки из таблицы your_table , в которых поле your_column содержит данные, исключая строки с NULL .
Нормализация базы данных
Улучшение структуры базы данных помогает уменьшить необходимость фильтрации NULL значений. Адекватная нормализация предусматривает установление ограничений NOT NULL для полей, которые не предполагают наличие NULL , что упрощает формирование запросов.
Оператор безопасного сравнения с NULL
Если NULL используется как уникальное значимое значение, предпочтительнее использовать оператор безопасного сравнения с NULL :
SQL условие IS NOT NULL
В этом учебном материале вы узнаете, как использовать SQL условие IS NOT NULL с синтаксисом и примерами.
Описание
Условие IS NOT NULL используется в SQL для проверки значения, отличного от NULL. Оно возвращает TRUE, если найдено ненулевое значение, в противном случае оно возвращает FALSE. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.
Синтаксис
Синтаксис для условия IS NOT NULL в SQL:
expression IS NOT NULL
Параметры или аргументы
expression Выражение для проверки значения NOT NULL.
Пример — использование IS NOT NULL с оператором SELECT
При проверке значения, отличного от NULL, IS NOT NULL является рекомендуемым оператором сравнения для использования в SQL. Давайте начнем с примера, который показывает, как использовать условие IS NOT NULL в SELECT предложении.
В этом примере у нас есть таблица products со следующими данными:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
| 7 | Kleenex | NULL |
Введите следующий SQL оператор:
FROM products
WHERE category_id IS NOT NULL ;
Будет выбрано 6 записей. Вот результаты, которые вы должны получить:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
В этом примере будут возвращены все записи из таблицы products , где customer_id не содержит значения NULL.
Пример — использование IS NOT NULL с оператором UPDATE
Далее давайте рассмотрим пример использования условия IS NOT NULL в запросе UPDATE.
В этом примере у нас есть таблица customer со следующими данными:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
Введите следующий запрос UPDATE:
UPDATE customers
SET favorite_website = ‘google.com’
WHERE favorite_website IS NOT NULL ;
Будет обновлено 5 записей. Выберите данные из таблицы customer еще раз:
FROM customers;
Вот результаты, которые вы должны получить:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | google.com |
| 6000 | Mila | Kunis | google.com |
| 7000 | Tom | Cruise | google.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
В этом примере будут обновлены все значения fav_website в таблице customer до google.com, где favourite_website содержит значение NULL. Как вы видите, значения поля favorite_website обновлены все строки кроме одной.
Пример — использование IS NOT NULL с оператором DELETE
Далее давайте рассмотрим пример использования условия IS NULL в запросе DELETE.
В этом примере у нас есть таблица orders и следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Введите следующий запрос DELETE:
COALESCE (Transact-SQL)
Вычисляет аргументы по порядку и возвращает текущее значение первого выражения, изначально не вычисленного как NULL . Например, SELECT COALESCE(NULL, NULL, ‘third_value’, ‘fourth_value’); возвращает третье значение, так как это первое значение, не равное NULL.
Синтаксис
COALESCE ( expression [ . n ] ) Аргументы
выражение
Выражение любого типа данных.
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Типы возвращаемых данных
Возвращает тип данных аргумента expression с наиболее высоким приоритетом. Если ни одно из выражений не допускает значения NULL, то результат типизируется как не допускающий значения NULL.
Замечания
Если все аргументы имеют значение NULL , COALESCE возвращает NULL . По крайней мере одно из значений NULL должно быть типизированным NULL .
Сравнение COALESCE и CASE
Выражение COALESCE — синтаксический ярлык для выражения CASE . Это означает, что код COALESCE (expression1,. n) переписывается оптимизатором запросов как следующее выражение CASE :
CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 . ELSE expressionN END Поэтому входные значения (expression1, expression2, expressionN и т. д.) вычисляются многократно. Выражение значения, содержащее вложенный запрос, считается недетерминированным, и вложенный запрос вычисляется дважды. Этот результат соответствует стандарту SQL. В любом случае могут быть возвращены различные результаты для первого и последующих вычислений.
Например, если выполняется код COALESCE((subquery), 1) , вложенный запрос вычисляется дважды. В результате можно получить различные результаты в зависимости от уровня изоляции запроса. Например, код может вернуть NULL при уровне изоляции READ COMMITTED в многопользовательской среде. Чтобы обеспечить устойчивые результаты, используйте уровень изоляции SNAPSHOT ISOLATION или замените COALESCE функцией ISNULL . Кроме того, можно переписать запрос, чтобы поместить вложенный запрос в подзапрос выборки, как показано в следующем примере:
SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END FROM ( SELECT (SELECT Nullable FROM Demo WHERE SomeCol = 1) AS x ) AS T; Сравнение COALESCE и ISNULL
Функция ISNULL и выражение COALESCE имеют аналогичные цели, но могут отличаться поведением.
- Так как ISNULL — это функция, она вычисляется только один раз. Как было сказано выше, входные значения для выражения COALESCE могут вычисляться несколько раз.
- Различается определение типа данных результирующего выражения. ISNULL использует тип данных первого параметра, COALESCE следует правилам выражения CASE и возвращает тип данных значения с самым высоким приоритетом.
- Для ISNULL и COALESCE различается допустимость значения NULL для результирующего выражения. Значение, возвращаемое ISNULL , никогда НЕ БЫВАЕТ нулевым (предполагается, что возвращаемое значение является ненулевым). В то время как функция COALESCE с параметрами, которые не допускают значение NULL, считается имеющей значение NULL . Таким образом, выражения ISNULL(NULL, 1) и COALESCE(NULL, 1) , несмотря на одинаковый синтаксис, имеют разные значения допустимости NULL. Эти выражения отличаются при использовании в вычисляемых столбцах, создании ограничений ключа или детерминировании возвращаемого значения определяемой пользователем скалярной функции для возможности индексации, как показано в приведенном ниже примере.
USE tempdb; GO -- This statement fails because the PRIMARY KEY cannot accept NULL values -- and the nullability of the COALESCE expression for col2 -- evaluates to NULL. CREATE TABLE #Demo ( col1 INTEGER NULL, col2 AS COALESCE(col1, 0) PRIMARY KEY, col3 AS ISNULL(col1, 0) ); -- This statement succeeds because the nullability of the -- ISNULL function evaluates AS NOT NULL. CREATE TABLE #Demo ( col1 INTEGER NULL, col2 AS COALESCE(col1, 0), col3 AS ISNULL(col1, 0) PRIMARY KEY ); Примеры
А. Выполнение простого примера
В следующем примере показано, как функция COALESCE выбирает из первого столбца данные, отличные от значения NULL. В этом примере используется база данных AdventureWorks2022.
SELECT Name, Class, Color, ProductNumber, COALESCE(Class, Color, ProductNumber) AS FirstNotNull FROM Production.Product; B. Выполнение сложного примера
В следующем примере таблица wages включает три столбца с данными о ежегодной заработной плате сотрудников: hourly_wage, salary и commission. Однако служащий получает только один тип выплат. Для определения общей оплаты для всех служащих используйте функцию COALESCE для получения не равных NULL значений столбцов hourly_wage , salary и commission .
SET NOCOUNT ON; GO USE tempdb; IF OBJECT_ID('dbo.wages') IS NOT NULL DROP TABLE wages; GO CREATE TABLE dbo.wages ( emp_id TINYINT IDENTITY, hourly_wage DECIMAL NULL, salary DECIMAL NULL, commission DECIMAL NULL, num_sales TINYINT NULL ); GO INSERT dbo.wages (hourly_wage, salary, commission, num_sales) VALUES (10.00, NULL, NULL, NULL), (20.00, NULL, NULL, NULL), (30.00, NULL, NULL, NULL), (40.00, NULL, NULL, NULL), (NULL, 10000.00, NULL, NULL), (NULL, 20000.00, NULL, NULL), (NULL, 30000.00, NULL, NULL), (NULL, 40000.00, NULL, NULL), (NULL, NULL, 15000, 3), (NULL, NULL, 25000, 2), (NULL, NULL, 20000, 6), (NULL, NULL, 14000, 4); GO SET NOCOUNT OFF; GO SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales) AS money) AS 'Total Salary' FROM dbo.wages ORDER BY 'Total Salary'; GO Total Salary ------------ 10000.00 20000.00 20800.00 30000.00 40000.00 41600.00 45000.00 50000.00 56000.00 62400.00 83200.00 120000.00 (12 row(s) affected) В. Простой пример
В приведенном ниже примере показано, как COALESCE выбирает данные из первого столбца, содержащего значение, отличное от NULL. В этом примере предполагается, что таблица Products содержит следующие данные:
Name Color ProductNumber ------------ ---------- ------------- Socks, Mens NULL PN1278 Socks, Mens Blue PN1965 NULL White PN9876 Затем выполняется следующий запрос COALESCE:
SELECT Name, Color, ProductNumber, COALESCE(Color, ProductNumber) AS FirstNotNull FROM Products ; Name Color ProductNumber FirstNotNull ------------ ---------- ------------- ------------ Socks, Mens NULL PN1278 PN1278 Socks, Mens Blue PN1965 Blue NULL White PN9876 White Обратите внимание на то, что в первой строке значение FirstNotNull равно PN1278 , а не Socks, Mens . Этот параметр принимает такое значение, так как столбец Name в примере не был указан в качестве параметра для COALESCE .
Г. Сложный пример
В приведенном ниже примере COALESCE используется для сравнения значений в трех столбцах и возврата только значения, отличного от NULL, найденного в столбцах.
CREATE TABLE dbo.wages ( emp_id TINYINT NULL, hourly_wage DECIMAL NULL, salary DECIMAL NULL, commission DECIMAL NULL, num_sales TINYINT NULL ); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (1, 10.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (2, 20.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (3, 30.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (4, 40.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (5, NULL, 10000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (6, NULL, 20000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (7, NULL, 30000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (8, NULL, 40000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (9, NULL, NULL, 15000, 3); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (10,NULL, NULL, 25000, 2); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (11, NULL, NULL, 20000, 6); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (12, NULL, NULL, 14000, 4); SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales) AS DECIMAL(10,2)) AS TotalSalary FROM dbo.wages ORDER BY TotalSalary; Total Salary ------------ 10000.00 20000.00 20800.00 30000.00 40000.00 41600.00 45000.00 50000.00 56000.00 62400.00 83200.00 120000.00 Значение NULL в SQL – неизвестное значение
Примечание:
Во всех статьях текущей категории уроков по SQL используются примеры и задачи, основанные на учебной базе данных.
Приступая к изучению данного материала, рекомендуется ознакомиться с описанием учебной БД.
Даже для небольших баз данных часто встречаются ситуации, когда значение какого-либо поля таблицы может быть неизвестно. Причины возникновения подобных ситуаций могут быть разными, начиная ошибками ПО и проектирования БД, заканчивая особенностями бизнес-процессов организации.
Если рассмотреть диаграмму таблицы сотрудников учебной БД, то можно заметить, что последний столбец диаграммы указывает возможность наличия неизвестных значение в конкретном поле, а именно:
- Отчество. Вполне возможно, что сотрудником является гражданин страны, где не используется отчество.
- Дата увольнения может отсутствовать, так как увольнения еще не было.
- Группа может быть неизвестна, потому что сотрудник может быть не распределен в группу на каком-то из этапов приема на работу.
Важно понять, что неизвестные (отсутствующие) значения – это не ноль (для числовых полей) и не пустая строка (для текстовых полей). Так как ноль является вполне конкретным значением, например, 0 рублей задолженности, а пустая строка сообщает о том, что на данный момент ничего кроме строки нулевой длины в поле строки быть не должно. В примере с отчеством, приведенном выше, вместо значения NULL можно задать пустую строку и это внесло бы дополнительную ясность, что отчество сотрудника нет в принципе, а не то, что его забыли внести.
Поиск отсутствующих значений
Выше было определено, что NULL не является конкретным значением, поэтому нужно понять, как операторы сравнения с ним будут работать. Никакое значение не может быть равно (также быть больше или меньше) неизвестному значению, даже условие NULL = NULL является ложным. Чтобы определить отсутствующее значения используется специальное условие IS NULL (является неизвестным). И наоборот, если требуется найти известные значения, то задается условие IS NOT NULL.
Рассмотрим задачу.
Найти всех сотрудников, которые были когда-либо уволены.
Решение.
Если в поле «Дата_увольнения» таблицы сотрудников отсутствует значение, то сотрудники работают на данный момент. Следовательно, нужно найти строки, где значение известно. Следующий sql-запрос выведет 7 строк, удовлетворяющих решению:
USE CallCenter SELECT * FROM Сотрудники WHERE Дата_увольнения IS NOT NULL
Решим еще одну задачу.
Вывести непринятые звонки за 1 декабря 2014 года.
Решение.
Звонок считается непринятым, если в таблице «Звонки» в поле «Сотрудник» отсутствует id принявшего звонок оператора. Отфильтровав таблицу по полям «Сотрудник» и «Дата_Время», получим 184 строки, удовлетворяющих запросу:
USE CallCenter SELECT * FROM Звонки WHERE Сотрудник IS NULL AND Дата_Время >= '01/12/2014 00:00:00' AND Дата_Время < '02/12/2014 00:00:00'
Обработка неизвестных значений
Если в своих запросах, Вы будете использовать поля, которые допускают значения NULL, то обязательно обрабатывайте такие поля, чтобы избежать ошибок. Например, любые арифметические операции или объединения строк, где в качестве аргумента будет хотя бы одно значение NULL, вернут неизвестное значение.
Рассмотрим пример.
Необходимо определить стаж работы каждого сотрудника, включая уволенных, на текущий момент. Стаж вывести в днях.
Для определения стажа необходимо найти интервал (разницу) между датой найма сотрудника и датой увольнения. Для этого можно использовать функцию DATEDIFF (ее описание можно найти в документации Microsoft). Но как быть с не уволенными сотрудниками, у которых отсутствует значение даты увольнения? Если выполнить ниже приведенный запрос, то можно убедиться, что большинство строк не покажут стаж:
USE CallCenter SELECT id, DATEDIFF(DAY, Дата_найма, Дата_увольнения) AS Стаж FROM Сотрудники
Поэтому обработаем поле «Дата_увольнения», применив функции ISNULL (если первый аргумент является NULL, то функция возвращает второй аргумент) и GETDATE (возвращает текущую системную дату и время). Следующий запрос выведет стаж в каждой строке:
USE CallCenter SELECT id, DATEDIFF(DAY, Дата_найма, ISNULL(Дата_увольнения, GETDATE())) AS Стаж FROM Сотрудники
- Объединение таблиц – UNION
- Соединение таблиц – операция JOIN и ее виды
- Тест на знание основ SQL
Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.