Isnull t sql: Функции COALESCE и ISNULL в T-SQL – особенности и основные отличия | Info-Comp.ru

SQL Значение NULL

Что такое значение NULL?

Поле с значением NULL — это поле без значения.

Если поле в таблице является необязательным,
то можно вставить новую запись или обновить запись без добавления значения в это поле.
Затем поле будет сохранено с значением NULL.

Примечание: Значение NULL отличается от нулевого значения или поля, содержащего пробелы.
Поле с значением NULL — это поле, которое было оставлено пустым во время создания записи!

Как проверить наличие NULL значений?

Невозможно проверить наличие нулевых значений с помощью операторов сравнения, таких как =, <, or <>.

Вместо этого нам придется использовать оператор IS NULL и IS NOT NULL.

Синтаксис IS NULL

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

Синтаксис IS NOT NULL

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Демо база данных

Ниже приведен выбор из таблицы «Customers» в образце базы данных Northwind:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

Оператор IS NULL

Оператор IS NULL используется для проверки пустых значений (NULL).

В следующем SQL файле перечислены все клиенты с значением NULL в поле «Address»:

Совет: Всегда используйте значение NULL для поиска значений NULL.

Оператор IS NOT NULL

Оператор IS NOT NULL используется для проверки непустых значений (NOT NULL).

В следующем SQL файле перечислены все клиенты со значением в поле «Address»:

SQL Подстановочный знак

SQL Подстановочные знаки

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

Подстановочные знаки используются с оператором SQL LIKE.
Оператор LIKE используется в предложении WHERE для поиска указанного шаблона в столбце.

Подстановочные знаки в MS Access

Подстановочные знаки в SQL Server

Все подстановочные знаки также могут быть использованы в комбинациях!

Вот несколько примеров, показывающих различные операторы LIKE с подстановочными знаками ‘%’ и ‘_’:

Демо база данных

Ниже приведен выбор из таблицы «Customers» в образце базы данных Northwind:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

Использование подстановочного знака %

Следующая инструкция SQL выбирает всех клиентов с городом, начинающимся с «ber»:

Следующая инструкция SQL выбирает всех клиентов с городом, содержащим шаблон «es»:

Использование подстановочного знака _

Следующая инструкция SQL выбирает всех клиентов с городом, начинающимся с любого символа, за которым следует «ondon»:

Следующая инструкция SQL выбирает всех клиентов с городом, начинающимся с «L»,
за которым следует любой символ, за которым следует «n», за которым следует любой символ, а затем «on»:

Использование подстановочного знака [charlist]

Следующая инструкция SQL выбирает всех клиентов с городом, начинающимся с «b», «s» или «p»:

Следующая инструкция SQL выбирает всех клиентов с городом, начинающимся с «a», «b» или «c»:

Использование подстановочного знака [!charlist]

В двух следующих инструкциях SQL выбираются все клиенты с городом, не начинающимся с «b», «s» или «p»:

Или:

Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть третья / Хабр

Предыдущие части

О чем будет рассказано в этой части

В этой части мы познакомимся:

1. с выражением CASE, которое позволяет включить условные выражения в запрос;
2. с агрегатными функциями, которые позволяют получить разного рода итоги (агрегированные значения) рассчитанные на основании детальных данных, полученных оператором «SELECT … WHERE …»;
3. с предложением GROUP BY, которое в скупе с агрегатными функциями позволяет получить итоги по детальным данным в разрезе групп;
4. с предложением HAVING, которое позволяет произвести фильтрацию по сгруппированным данным.

Выражение CASE – условный оператор языка SQL

Данный оператор позволяет осуществить проверку условий и возвратить в зависимости от выполнения того или иного условия тот или иной результат.

Оператор CASE имеет 2 формы:

В качестве значений здесь могут выступать и выражения.

Разберем на примере первую форму CASE:

SELECT
  ID,Name,Salary,

  CASE
    WHEN Salary>=3000 THEN 'ЗП >= 3000'
    WHEN Salary>=2000 THEN '2000 <= ЗП < 3000'
    ELSE 'ЗП < 2000'
  END SalaryTypeWithELSE,

  CASE
    WHEN Salary>=3000 THEN 'ЗП >= 3000'
    WHEN Salary>=2000 THEN '2000 <= ЗП < 3000'
  END SalaryTypeWithoutELSE

FROM Employees

ID	Name	Salary	SalaryTypeWithELSE	SalaryTypeWithoutELSE
1000	Иванов И.И.	5000	ЗП >= 3000	ЗП >= 3000
1001	Петров П.П.	1500	ЗП < 2000	NULL
1002	Сидоров С.С.	2500	2000 <= ЗП < 3000	2000 <= ЗП < 3000
1003	Андреев А.А.	2000	2000 <= ЗП < 3000	2000 <= ЗП < 3000
1004	Николаев Н. Н.	1500	ЗП < 2000	NULL
1005	Александров А.А.	2000	2000 <= ЗП < 3000	2000 <= ЗП < 3000

WHEN-условия проверяются последовательно, сверху-вниз. При достижении первого удовлетворяющего условия дальнейшая проверка прерывается и возвращается значение, указанное после слова THEN, относящегося к данному блоку WHEN.

Если ни одно из WHEN-условий не выполняется, то возвращается значение, указанное после слова ELSE (что в данном случае означает «ИНАЧЕ ВЕРНИ …»).

Если ELSE-блок не указан и не выполняется ни одно WHEN-условие, то возвращается NULL.

И в первой, и во второй форме ELSE-блок идет в самом конце конструкции CASE, т.е. после всех WHEN-условий.

Разберем на примере вторую форму CASE:

Допустим, на новый год решили премировать всех сотрудников и попросили вычислить сумму бонусов по следующей схеме:

• Сотрудникам ИТ-отдела выдать по 15% от ЗП;
• Сотрудникам Бухгалтерии по 10% от ЗП;
• Всем остальным по 5% от ЗП.

Используем для данной задачи запрос с выражением CASE:

SELECT
  ID,Name,Salary,DepartmentID,

  -- для наглядности выведем процент в виде строки
  CASE DepartmentID -- проверяемое значение
    WHEN 2 THEN '10%' -- 10% от ЗП выдать Бухгалтерам
    WHEN 3 THEN '15%' -- 15% от ЗП выдать ИТ-шникам
    ELSE '5%' -- всем остальным по 5%
  END NewYearBonusPercent,

  -- построим выражение с использованием CASE, чтобы увидеть сумму бонуса
  Salary/100*
  CASE DepartmentID
    WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
    WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
    ELSE 5 -- всем остальным по 5%
  END BonusAmount

FROM Employees

ID	Name	Salary	DepartmentID	NewYearBonusPercent	BonusAmount
1000	Иванов И.И.	5000	1	5%	250
1001	Петров П. П.	1500	3	15%	225
1002	Сидоров С.С.	2500	2	10%	250
1003	Андреев А.А.	2000	3	15%	300
1004	Николаев Н.Н.	1500	3	15%	225
1005	Александров А.А.	2000	NULL	5%	100

Здесь делается последовательная проверка значения DepartmentID с WHEN-значениями. При достижении первого равенства DepartmentID с WHEN-значением, проверка прерывается и возвращается значение, указанное после слова THEN, относящегося к данному блоку WHEN.

Соответственно, значение блока ELSE возвращается в случае, если DepartmentID не совпал ни с одним WHEN-значением.

Если блок ELSE отсутствует, то в случае несовпадения DepartmentID ни с одним WHEN-значением будет возвращено NULL.

Вторую форму CASE несложно представить при помощи первой формы:

SELECT
  ID,Name,Salary,DepartmentID,

  CASE
    WHEN DepartmentID=2 THEN '10%' -- 10% от ЗП выдать Бухгалтерам
    WHEN DepartmentID=3 THEN '15%' -- 15% от ЗП выдать ИТ-шникам
    ELSE '5%' -- всем остальным по 5%
  END NewYearBonusPercent,

  -- построим выражение с использованием CASE, чтобы увидеть сумму бонуса
  Salary/100*
  CASE
    WHEN DepartmentID=2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
    WHEN DepartmentID=3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
    ELSE 5 -- всем остальным по 5%
  END BonusAmount

FROM Employees

Так что, вторая форма – это всего лишь упрощенная запись для тех случаев, когда нам нужно сделать сравнение на равенство, одного и того же проверяемого значения с каждым WHEN-значением/выражением.

Примечание. Первая и вторая форма CASE входят в стандарт языка SQL, поэтому скорее всего они должны быть применимы во многих СУБД.

С MS SQL версии 2012 появилась упрощенная форма записи IIF. Она может использоваться для упрощенной записи конструкции CASE, в том случае если возвращаются только 2 значения. Конструкция IIF имеет следующий вид:

IIF(условие, true_значение, false_значение)

Т.е. по сути это обертка для следующей CASE конструкции:

CASE WHEN условие THEN true_значение ELSE false_значение END

Посмотрим на примере:

SELECT
  ID,Name,Salary,

  IIF(Salary>=2500,'ЗП >= 2500','ЗП < 2500') DemoIIF,

  CASE WHEN Salary>=2500 THEN 'ЗП >= 2500' ELSE 'ЗП < 2500' END DemoCASE

FROM Employees

Конструкции CASE, IIF могут быть вложенными друг в друга. Рассмотрим абстрактный пример:

SELECT
  ID,Name,Salary,

  CASE
    WHEN DepartmentID IN(1,2) THEN 'A'
    WHEN DepartmentID=3 THEN
                          CASE PositionID -- вложенный CASE
                            WHEN 3 THEN 'B-1'
                            WHEN 4 THEN 'B-2'
                          END
    ELSE 'C'
  END Demo1,

  IIF(DepartmentID IN(1,2),'A',
    IIF(DepartmentID=3,CASE PositionID WHEN 3 THEN 'B-1' WHEN 4 THEN 'B-2' END,'C')) Demo2

FROM Employees

Так как конструкция CASE и IIF представляют из себя выражение, которые возвращают результат, то мы можем использовать их не только в блоке SELECT, но и в остальных блоках, допускающих использование выражений, например, в блоках WHERE или ORDER BY.

Для примера, пускай перед нами поставили задачу – создать список на выдачу ЗП на руки, следующим образом:

• Первым делом ЗП должны получить сотрудники у кого оклад меньше 2500
• Те сотрудники у кого оклад больше или равен 2500, получают ЗП во вторую очередь
• Внутри этих двух групп нужно упорядочить строки по ФИО (поле Name)

Попробуем решить эту задачу при помощи добавления CASE-выражение в блок ORDER BY:

SELECT
  ID,Name,Salary
FROM Employees
ORDER BY
  CASE WHEN Salary>=2500 THEN 1 ELSE 0 END, -- выдать ЗП сначала тем у кого она ниже 2500
  Name -- дальше упорядочить список в порядке ФИО

ID	Name	Salary
1005	Александров А.А.	2000
1003	Андреев А.А.	2000
1004	Николаев Н.Н.	1500
1001	Петров П. П.	1500
1000	Иванов И.И.	5000
1002	Сидоров С.С.	2500

Как видим, Иванов и Сидоров уйдут с работы последними.

И абстрактный пример использования CASE в блоке WHERE:

SELECT
  ID,Name,Salary
FROM Employees
WHERE CASE WHEN Salary>=2500 THEN 1 ELSE 0 END=1 -- все записи у которых выражение равно 1

Можете попытаться самостоятельно переделать 2 последних примера с функцией IIF.

И напоследок, вспомним еще раз о NULL-значениях:

SELECT
  ID,Name,Salary,DepartmentID,

  CASE
    WHEN DepartmentID=2 THEN '10%' -- 10% от ЗП выдать Бухгалтерам
    WHEN DepartmentID=3 THEN '15%' -- 15% от ЗП выдать ИТ-шникам
    WHEN DepartmentID IS NULL THEN '-' -- внештатникам бонусов не даем (используем IS NULL)
    ELSE '5%' -- всем остальным по 5%
  END NewYearBonusPercent1,

  -- а так проверять на NULL нельзя, вспоминаем что говорилось про NULL во второй части
  CASE DepartmentID -- проверяемое значение
    WHEN 2 THEN '10%'
    WHEN 3 THEN '15%'
    WHEN NULL THEN '-' -- !!! в данном случае использование второй формы CASE не подходит
    ELSE '5%'
  END NewYearBonusPercent2

FROM Employees

ID	Name	Salary	DepartmentID	NewYearBonusPercent1	NewYearBonusPercent2
1000	Иванов И. И.	5000	1	5%	5%
1001	Петров П.П.	1500	3	15%	15%
1002	Сидоров С.С.	2500	2	10%	10%
1003	Андреев А.А.	2000	3	15%	15%
1004	Николаев Н.Н.	1500	3	15%	15%
1005	Александров А.А.	2000	NULL	—	5%

Конечно можно было переписать и как-то так:

SELECT
  ID,Name,Salary,DepartmentID,

  CASE ISNULL(DepartmentID,-1) -- используем замену в случае NULL на -1
    WHEN 2 THEN '10%'
    WHEN 3 THEN '15%'
    WHEN -1 THEN '-' -- если мы уверены, что отдела с ID равным (-1) нет и не будет
    ELSE '5%'
  END NewYearBonusPercent3

FROM Employees

В общем, полет фантазии в данном случае не ограничен.

Для примера посмотрим, как при помощи CASE и IIF можно смоделировать функцию ISNULL:

SELECT
  ID,Name,LastName,

  ISNULL(LastName,'Не указано') DemoISNULL,
  CASE WHEN LastName IS NULL THEN 'Не указано' ELSE LastName END DemoCASE,
  IIF(LastName IS NULL,'Не указано',LastName) DemoIIF
FROM Employees

Конструкция CASE очень мощное средство языка SQL, которое позволяет наложить дополнительную логику для расчета значений результирующего набора. В данной части владение CASE-конструкцией нам еще пригодится, поэтому в этой части в первую очередь внимание уделено именно ей.

Агрегатные функции

Здесь мы рассмотрим только основные и наиболее часто используемые агрегатные функции:

Название	Описание
COUNT(*)	Возвращает количество строк полученных оператором «SELECT … WHERE …». В случае отсутствии WHERE, количество всех записей таблицы.
COUNT(столбец/выражение)	Возвращает количество значений (не равных NULL), в указанном столбце/выражении
COUNT(DISTINCT столбец/выражение)	Возвращает количество уникальных значений, не равных NULL в указанном столбце/выражении
SUM(столбец/выражение)	Возвращает сумму по значениям столбца/выражения
AVG(столбец/выражение)	Возвращает среднее значение по значениям столбца/выражения. NULL значения для подсчета не учитываются.
MIN(столбец/выражение)	Возвращает минимальное значение по значениям столбца/выражения
MAX(столбец/выражение)	Возвращает максимальное значение по значениям столбца/выражения

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

Рассмотрим каждую функцию на примере:

SELECT
  COUNT(*) [Общее кол-во сотрудников],
  COUNT(DISTINCT DepartmentID) [Число уникальных отделов],
  COUNT(DISTINCT PositionID) [Число уникальных должностей],
  COUNT(BonusPercent) [Кол-во сотрудников у которых указан % бонуса],
  MAX(BonusPercent) [Максимальный процент бонуса],
  MIN(BonusPercent) [Минимальный процент бонуса],
  SUM(Salary/100*BonusPercent) [Сумма всех бонусов],
  AVG(Salary/100*BonusPercent) [Средний размер бонуса],
  AVG(Salary) [Средний размер ЗП]
FROM Employees

Общее кол-во сотрудников	Число уникальных отделов	Число уникальных должностей	Кол-во сотрудников у которых указан % бонуса	Максимальный процент бонуса	Минимальный процент бонуса	Сумма всех бонусов	Средний размер бонуса	Средний размер ЗП
6	3	4	3	50	15	3325	1108. 33333333333	2416.66666666667

Для большей наглядности я решил здесь сделать исключение и воспользовался синтаксисом […] для задания псевдонимов колонок.

Разберем каким образом получилось каждое возвращенное значение, а за одно вспомним конструкции базового синтаксиса оператора SELECT.

Во-первых, т.к. мы в запросе не указали WHERE-условия, то итоги будут считаться для детальных данных, которые получаются запросом:

SELECT * FROM Employees

т.е. для всех строк таблицы Employees.

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

SELECT
  DepartmentID,
  PositionID,
  BonusPercent,
  Salary/100*BonusPercent [Salary/100*BonusPercent],
  Salary
FROM Employees

Это исходные данные (детальные строки), по которым и будут считаться итоги агрегированного запроса.

Теперь разберем каждое агрегированное значение:

COUNT(*) – т.к. мы не задали в запросе условия фильтрации в блоке WHERE, то COUNT(*) дало нам общее количество записей в таблице, т.е. это количество строк, которое возвращает запрос: SELECT * FROM Employees

COUNT(DISTINCT DepartmentID) – вернуло нам значение 3, т.е. это число соответствует числу уникальных значений департаментов указанных в столбце DepartmentID без учета NULL значений. Пройдемся по значениям колонки DepartmentID и раскрасим одинаковые значения в один цвет (не стесняйтесь, для обучения все методы хороши): Отбрасываем NULL, после чего, мы получили 3 уникальных значения (1, 2 и 3). Т.е. значение получаемое COUNT(DISTINCT DepartmentID), в развернутом виде можно представить следующей выборкой: SELECT DISTINCT DepartmentID -- 2. берем только уникальные значения FROM Employees WHERE DepartmentID IS NOT NULL -- 1. отбрасываем NULL значения

COUNT(DISTINCT PositionID) – то же самое, что было сказано про COUNT(DISTINCT DepartmentID), только полю PositionID. Смотрим на значения колонки PositionID и не жалеем красок:

COUNT(BonusPercent) – возвращает количество строк, у которых указано значение BonusPercent, т.е. подсчитывается количество записей, у которых BonusPercent IS NOT NULL. Здесь нам будет проще, т.к. не нужно считать уникальные значения, достаточно просто отбросить записи с NULL значениями. Берем значения колонки BonusPercent и вычеркиваем все NULL значения: Остается 3 значения. Т.е. в развернутом виде выборку можно представить так: SELECT BonusPercent -- 2. берем все значения FROM Employees WHERE BonusPercent IS NOT NULL -- 1. отбрасываем NULL значения Т.к. мы не использовали слова DISTINCT, то посчитаются и повторяющиеся BonusPercent в случае их наличия, без учета BonusPercent равных NULL. Для примера давайте сделаем сравнение результата с использованием DISTINCT и без него. Для большей наглядности воспользуемся значениями поля DepartmentID: SELECT COUNT(*), -- 6 COUNT(DISTINCT DepartmentID), -- 3 COUNT(DepartmentID) -- 5 FROM Employees

MAX(BonusPercent) – возвращает максимальное значение BonusPercent, опять же без учета NULL значений. Берем значения колонки BonusPercent и ищем среди них максимальное значение, на NULL значения не обращаем внимания: Т.е. мы получаем следующее значение: SELECT TOP 1 BonusPercent FROM Employees WHERE BonusPercent IS NOT NULL ORDER BY BonusPercent DESC -- сортируем по убыванию

MIN(BonusPercent) – возвращает минимальное значение BonusPercent, опять же без учета NULL значений. Как в случае с MAX, только ищем минимальное значение, игнорируя NULL: Т.е. мы получаем следующее значение: SELECT TOP 1 BonusPercent FROM Employees WHERE BonusPercent IS NOT NULL ORDER BY BonusPercent -- сортируем по возрастанию Наглядное представление MIN(BonusPercent) и MAX(BonusPercent):

SUM(Salary/100*BonusPercent) – возвращает сумму всех не NULL значений. Разбираем значения выражения (Salary/100*BonusPercent): Т.е. происходит суммирование следующих значений: SELECT Salary/100*BonusPercent FROM Employees WHERE Salary/100*BonusPercent IS NOT NULL

AVG(Salary/100*BonusPercent) – возвращает среднее значений. NULL-выражения не учитываются, т.е. это соответствует второму выражению: SELECT AVG(Salary/100*BonusPercent), -- 1108.33333333333 SUM(Salary/100*BonusPercent)/COUNT(Salary/100*BonusPercent), -- 1108.33333333333 SUM(Salary/100*BonusPercent)/COUNT(*) -- 554.166666666667 FROM Employees Т.е. опять же NULL-значения не учитываются при подсчете количества. Если же вам необходимо вычислить среднее по всем сотрудникам, как в третьем выражении, которое дает 554.166666666667, то используйте предварительное преобразование NULL значений в ноль: SELECT AVG(ISNULL(Salary/100*BonusPercent,0)), -- 554.166666666667 SUM(Salary/100*BonusPercent)/COUNT(*) -- 554. 166666666667 FROM Employees

AVG(Salary) – собственно, здесь все то же самое что и в предыдущем случае, т.е. если у сотрудника Salary равен NULL, то он не учтется. Чтобы учесть всех сотрудников, соответственно делаете предварительное преобразование NULL значений AVG(ISNULL(Salary,0))

Подведем некоторые итоги:

• COUNT(*) – служит для подсчета общего количества строк, которые получены оператором «SELECT … WHERE …»
• во всех остальных вышеперечисленных агрегатных функциях при расчете итога, NULL-значения не учитываются
• если нам нужно учесть все строки, это больше актуально для функции AVG, то предварительно необходимо осуществить обработку NULL значений, например, как было показано выше «AVG(ISNULL(Salary,0))»

Соответственно при задании с агрегатными функциями дополнительного условия в блоке WHERE, будут подсчитаны только итоги, по строкам удовлетворяющих условию. Т.е. расчет агрегатных значений происходит для итогового набора, который получен при помощи конструкции SELECT. Например, сделаем все тоже самое, но только в разрезе ИТ-отдела:

SELECT
  COUNT(*) [Общее кол-во сотрудников],
  COUNT(DISTINCT DepartmentID) [Число уникальных отделов],
  COUNT(DISTINCT PositionID) [Число уникальных должностей],
  COUNT(BonusPercent) [Кол-во сотрудников у которых указан % бонуса],
  MAX(BonusPercent) [Максимальный процент бонуса],
  MIN(BonusPercent) [Минимальный процент бонуса],
  SUM(Salary/100*BonusPercent) [Сумма всех бонусов],
  AVG(Salary/100*BonusPercent) [Средний размер бонуса],
  AVG(Salary) [Средний размер ЗП]
FROM Employees
WHERE DepartmentID=3 -- учесть только ИТ-отдел

Общее кол-во сотрудников	Число уникальных отделов	Число уникальных должностей	Кол-во сотрудников у которых указан % бонуса	Максимальный процент бонуса	Минимальный процент бонуса	Сумма всех бонусов	Средний размер бонуса	Средний размер ЗП
3	1	2	2	30	15	825	412. 5	1666.66666666667

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

SELECT
  DepartmentID,
  PositionID,
  BonusPercent,
  Salary/100*BonusPercent [Salary/100*BonusPercent],
  Salary
FROM Employees
WHERE DepartmentID=3 -- учесть только ИТ-отдел

Идем, дальше. В случае, если агрегатная функция возвращает NULL (например, у всех сотрудников не указано значение Salary), или в выборку не попало ни одной записи, а в отчете, для такого случая нам нужно показать 0, то функцией ISNULL можно обернуть агрегатное выражение:

SELECT
  SUM(Salary),
  AVG(Salary),

  -- обрабатываем итог при помощи ISNULL
  ISNULL(SUM(Salary),0),
  ISNULL(AVG(Salary),0)
FROM Employees
WHERE DepartmentID=10 -- здесь специально указан несуществующий отдел, чтобы запрос не вернул записей

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

В данном случае мы рассмотрели, как каждая агрегатная функция ведет себя самостоятельно, т.е. она применялась к значениям всего набора записей полученным командой SELECT. Дальше мы рассмотрим, как эти же функции применяются для вычисления итогов по группам, при помощи конструкции GROUP BY.

GROUP BY – группировка данных

До этого мы уже вычисляли итоги для конкретного отдела, примерно следующим образом:

SELECT
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=3 -- данные только по ИТ отделу

А теперь представьте, что нас попросили получить такие же цифры в разрезе каждого отдела. Конечно мы можем засучить рукава и выполнить этот же запрос для каждого отдела. Итак, сказано-сделано, пишем 4 запроса:

SELECT
  'Администрация' Info,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=1 -- данные по Администрации

SELECT
  'Бухгалтерия' Info,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=2 -- данные по Бухгалтерии

SELECT
  'ИТ' Info,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=3 -- данные по ИТ отделу

SELECT
  'Прочие' Info,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID IS NULL -- и еще не забываем данные по внештатникам

В результате мы получим 4 набора данных:

Обратите внимание, что мы можем использовать поля, заданные в виде констант – ‘Администрация’, ‘Бухгалтерия’, …

В общем все цифры, о которых нас просили, мы добыли, объединяем все в Excel и отдаем директору.

Отчет директору понравился, и он говорит: «а добавьте еще колонку с информацией по среднему окладу». И как всегда это нужно сделать очень срочно.

Мда, что делать?! Вдобавок представим еще что отделов у нас не 3, а 15.

Вот как раз то примерно для таких случаев служит конструкция GROUP BY:

SELECT
  DepartmentID,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount,
  AVG(Salary) SalaryAvg -- плюс выполняем пожелание директора
FROM Employees
GROUP BY DepartmentID

DepartmentID	PositionCount	EmplCount	SalaryAmount	SalaryAvg
NULL	0	1	2000	2000
1	1	1	5000	5000
2	1	1	2500	2500
3	2	3	5000	1666. 66666666667

Мы получили все те же самые данные, но теперь используя только один запрос!

Пока не обращайте внимание, на то что департаменты у нас вывелись в виде цифр, дальше мы научимся выводить все красиво.

В предложении GROUP BY можно указывать несколько полей «GROUP BY поле1, поле2, …, полеN», в этом случае группировка произойдет по группам, которые образовывают значения данных полей «поле1, поле2, …, полеN».

Для примера, сделаем группировку данных в разрезе Отделов и Должностей:

SELECT
  DepartmentID,PositionID,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID,PositionID

Давайте, теперь на этом примере, попробуем разобраться как работает GROUP BY

Для полей, перечисленных после GROUP BY из таблицы Employees определяются все уникальные комбинации по значениям DepartmentID и PositionID, т. е. происходит примерно следующее:

SELECT DISTINCT DepartmentID,PositionID
FROM Employees

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

SELECT
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE  DepartmentID IS NULL AND PositionID IS NULL

SELECT
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE  DepartmentID=1 AND PositionID=2

-- ...

SELECT
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE  DepartmentID=3 AND PositionID=4

А потом все эти результаты объединяются вместе и отдаются нам в виде одного набора:

Из основного, стоит отметить, что в случае группировки (GROUP BY), в перечне колонок в блоке SELECT:

• Мы можем использовать только колонки, перечисленные в блоке GROUP BY
• Можно использовать выражения с полями из блока GROUP BY
• Можно использовать константы, т. к. они не влияют на результат группировки
• Все остальные поля (не перечисленные в блоке GROUP BY) можно использовать только с агрегатными функциями (COUNT, SUM, MIN, MAX, …)
• Не обязательно перечислять все колонки из блока GROUP BY в списке колонок SELECT

И демонстрация всего сказанного:

SELECT
  'Строка константа' Const1, -- константа в виде строки
  1 Const2, -- константа в виде числа

  -- выражение с использованием полей участвуещих в группировке
  CONCAT('Отдел № ',DepartmentID) ConstAndGroupField, 
  CONCAT('Отдел № ',DepartmentID,', Должность № ',PositionID) ConstAndGroupFields,

  DepartmentID, -- поле из списка полей участвующих в группировке
  -- PositionID, -- поле учавствующее в группировке, не обязательно дублировать здесь

  COUNT(*) EmplCount, -- кол-во строк в каждой группе

  -- остальные поля можно использовать только с агрегатными функциями: COUNT, SUM, MIN, MAX, …
  SUM(Salary) SalaryAmount,
  MIN(ID) MinID
FROM Employees
GROUP BY DepartmentID,PositionID -- группировка по полям DepartmentID,PositionID

Так же стоит отметить, что группировку можно делать не только по полям, но также и по выражениям. Для примера сгруппируем данные по сотрудникам, по годам рождения:

SELECT
  CONCAT('Год рождения - ',YEAR(Birthday)) YearOfBirthday,
  COUNT(*) EmplCount
FROM Employees
GROUP BY YEAR(Birthday)

Рассмотрим пример с более сложным выражением. Для примера, получим градацию сотрудников по годам рождения:

SELECT
  CASE
    WHEN YEAR(Birthday)>=2000 THEN 'от 2000'
    WHEN YEAR(Birthday)>=1990 THEN '1999-1990'
    WHEN YEAR(Birthday)>=1980 THEN '1989-1980'
    WHEN YEAR(Birthday)>=1970 THEN '1979-1970'
    WHEN Birthday IS NOT NULL THEN 'ранее 1970'
    ELSE 'не указано'
  END RangeName,
  COUNT(*) EmplCount
FROM Employees
GROUP BY
  CASE
    WHEN YEAR(Birthday)>=2000 THEN 'от 2000'
    WHEN YEAR(Birthday)>=1990 THEN '1999-1990'
    WHEN YEAR(Birthday)>=1980 THEN '1989-1980'
    WHEN YEAR(Birthday)>=1970 THEN '1979-1970'
    WHEN Birthday IS NOT NULL THEN 'ранее 1970'
    ELSE 'не указано'
  END

Т. е. в данном случае группировка делается по предварительно вычисленному для каждого сотрудника CASE-выражению:

SELECT
  ID,
  CASE
    WHEN YEAR(Birthday)>=2000 THEN 'от 2000'
    WHEN YEAR(Birthday)>=1990 THEN '1999-1990'
    WHEN YEAR(Birthday)>=1980 THEN '1989-1980'
    WHEN YEAR(Birthday)>=1970 THEN '1979-1970'
    WHEN Birthday IS NOT NULL THEN 'ранее 1970'
    ELSE 'не указано'
  END
FROM Employees

Ну и конечно же вы можете объединять в блоке GROUP BY выражения с полями:

SELECT
  DepartmentID,
  CONCAT('Год рождения - ',YEAR(Birthday)) YearOfBirthday,
  COUNT(*) EmplCount
FROM Employees
GROUP BY YEAR(Birthday),DepartmentID -- порядок может не совпадать с порядком их использования в блоке SELECT
ORDER BY DepartmentID,YearOfBirthday -- напоследок мы можем применить к результату сортировку

Вернемся к нашей изначальной задаче. Как мы уже знаем, отчет очень понравился директору, и он попросил нас делать его еженедельно, дабы он мог мониторить изменения по компании. Чтобы, не перебивать каждый раз в Excel цифровое значение отдела на его наименование, воспользуемся знаниями, которые у нас уже есть, и усовершенствуем наш запрос:

SELECT
  CASE DepartmentID
    WHEN 1 THEN 'Администрация'
    WHEN 2 THEN 'Бухгалтерия'
    WHEN 3 THEN 'ИТ'
    ELSE 'Прочие'
  END Info,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount,
  AVG(Salary) SalaryAvg -- плюс выполняем пожелание директора
FROM Employees
GROUP BY DepartmentID
ORDER BY Info -- добавим для большего удобства сортировку по колонке Info

Info	PositionCount	EmplCount	SalaryAmount	SalaryAvg
Администрация	1	1	5000	5000
Бухгалтерия	1	1	2500	2500
ИТ	2	3	5000	1666. 66666666667
Прочие	0	1	2000	2000

Хоть со стороны может выглядит и страшно, но все равно это получше чем было изначально. Недостаток в том, что если заведут новый отдел и его сотрудников, то выражение CASE нам нужно будет дописывать, дабы сотрудники нового отдела не попали в группу «Прочие».

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

SELECT
  ISNULL(dep.Name,'Прочие') DepName,
  COUNT(DISTINCT emp.PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(emp.Salary) SalaryAmount,
  AVG(emp.Salary) SalaryAvg -- плюс выполняем пожелание директора
FROM Employees emp
LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID
GROUP BY emp.DepartmentID,dep.Name
ORDER BY DepName

В общем, не переживайте – все начинали с простого. Пока вам просто нужно понять суть конструкции GROUP BY.

Напоследок, давайте посмотрим каким образом можно строить сводные отчеты при помощи GROUP BY.

Для примера выведем сводную таблицу, в разрезе отделов, так чтобы была подсчитана суммарная заработная плата, получаемая сотрудниками в разбивке по должностям:

SELECT
  DepartmentID,
  SUM(CASE WHEN PositionID=1 THEN Salary END) [Бухгалтера],
  SUM(CASE WHEN PositionID=2 THEN Salary END) [Директора],
  SUM(CASE WHEN PositionID=3 THEN Salary END) [Программисты],
  SUM(CASE WHEN PositionID=4 THEN Salary END) [Старшие программисты],
  SUM(Salary) [Итого по отделу]
FROM Employees
GROUP BY DepartmentID

Т. е. мы свободно можем использовать любые выражения внутри агрегатных функций.

Можно конечно переписать и при помощи IIF:

SELECT
  DepartmentID,
  SUM(IIF(PositionID=1,Salary,NULL)) [Бухгалтера],
  SUM(IIF(PositionID=2,Salary,NULL)) [Директора],
  SUM(IIF(PositionID=3,Salary,NULL)) [Программисты],
  SUM(IIF(PositionID=4,Salary,NULL)) [Старшие программисты],
  SUM(Salary) [Итого по отделу]
FROM Employees
GROUP BY DepartmentID

Но в случае с IIF нам придется явно указывать NULL, которое возвращается в случае невыполнения условия.

В аналогичных случаях мне больше нравится использовать CASE без блока ELSE, чем лишний раз писать NULL. Но это конечно дело вкуса, о котором не спорят.

И давайте вспомним, что в агрегатных функциях при агрегации не учитываются NULL значения.

Для закрепления, сделайте самостоятельный анализ полученных данных по развернутому запросу:

SELECT
  DepartmentID,
  CASE WHEN PositionID=1 THEN Salary END [Бухгалтера],
  CASE WHEN PositionID=2 THEN Salary END [Директора],
  CASE WHEN PositionID=3 THEN Salary END [Программисты],
  CASE WHEN PositionID=4 THEN Salary END [Старшие программисты],
  Salary [Итого по отделу]
FROM Employees

И еще давайте вспомним, что если вместо NULL мы хотим увидеть нули, то мы можем обработать значение, возвращаемое агрегатной функцией. Например:

SELECT
  DepartmentID,
  ISNULL(SUM(IIF(PositionID=1,Salary,NULL)),0) [Бухгалтера],
  ISNULL(SUM(IIF(PositionID=2,Salary,NULL)),0) [Директора],
  ISNULL(SUM(IIF(PositionID=3,Salary,NULL)),0) [Программисты],
  ISNULL(SUM(IIF(PositionID=4,Salary,NULL)),0) [Старшие программисты],
  ISNULL(SUM(Salary),0) [Итого по отделу]
FROM Employees
GROUP BY DepartmentID

Теперь в целях практики, вы можете:

• вывести названия департаментов вместо их идентификаторов, например, добавив выражение CASE обрабатывающее DepartmentID в блоке SELECT
• добавьте сортировку по имени отдела при помощи ORDER BY

GROUP BY в скупе с агрегатными функциями, одно из основных средств, служащих для получения сводных данных из БД, ведь обычно данные в таком виде и используются, т. к. обычно от нас требуют предоставления сводных отчетов, а не детальных данных (простыней). И конечно же все это крутится вокруг знания базовой конструкции, т.к. прежде чем что-то подытожить (агрегировать), вам нужно первым делом это правильно выбрать, используя «SELECT … WHERE …».

Важное место здесь имеет практика, поэтому, если вы поставили целью понять язык SQL, не изучить, а именно понять – практикуйтесь, практикуйтесь и практикуйтесь, перебирая самые разные варианты, которые только сможете придумать.

На начальных порах, если вы не уверены в правильности полученных агрегированных данных, делайте детальную выборку, включающую все значения, по которым идет агрегация. И проверяйте правильность расчетов вручную по этим детальным данным. В этом случае очень сильно может помочь использование программы Excel.

Допустим, что вы дошли до этого момента

Допустим, что вы бухгалтер Сидоров С.С., который решил научиться писать SELECT-запросы.
Допустим, что вы уже успели дочитать данный учебник до этого момента, и уже уверено пользуетесь всеми вышеперечисленными базовыми конструкциями, т. е. вы умеете:

• Выбирать детальные данные по условию WHERE из одной таблицы
• Умеете пользоваться агрегатными функциями и группировкой из одной таблицы

Так как на работе посчитали, что вы уже все умеете, то вам предоставили доступ к БД (и такое порой бывает), и теперь вы разработали и вытаскиваете тот самый еженедельный отчет для директора.

Да, но они не учли, что вы пока не умеете строить запросы из нескольких таблиц, а только из одной, т.е. вы не умеете делать что-то вроде такого:

SELECT
  emp.*, -- вернуть все поля таблицы Employees
  dep.Name DepartmentName, -- к этим полям добавить поле Name из таблицы Departments
  pos.Name PositionName -- и еще добавить поле Name из таблицы Positions
FROM Employees emp
LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID
LEFT JOIN Positions pos ON emp.PositionID=pos.ID

Несмотря на то, что вы этого не умеете, поверьте, вы молодец, и уже, и так много достигли.

И так, как же можно воспользоваться вашими текущими знаниями и получить при этом еще более продуктивные результаты?! Воспользуемся силой коллективного разума – идем к программистам, которые работают у вас, т.е. к Андрееву А.А., Петрову П.П. или Николаеву Н.Н., и попросим кого-нибудь из них написать для вас представление (VIEW или просто «Вьюха», так они даже, думаю, быстрее поймут вас), которое помимо основных полей из таблицы Employees, будет еще возвращать поля с «Названием отдела» и «Названием должности», которых вам так недостает сейчас для еженедельного отчета, которым вас загрузил Иванов И.И.

Т.к. вы все грамотно объяснили, то ИТ-шники, сразу же поняли, что от них хотят и создали, специально для вас, представление с названием ViewEmployeesInfo.

Представляем, что вы следующей команды не видите, т.к. это делают ИТ-шники:

CREATE VIEW ViewEmployeesInfo
AS
SELECT
  emp.*, -- вернуть все поля таблицы Employees
  dep.Name DepartmentName, -- к этим полям добавить поле Name из таблицы Departments
  pos.Name PositionName -- и еще добавить поле Name из таблицы Positions
FROM Employees emp
LEFT JOIN Departments dep ON emp.DepartmentID=dep.ID
LEFT JOIN Positions pos ON emp.PositionID=pos.ID

Т.е. для вас весь этот, пока страшный и непонятный, текст остается за кадром, а ИТ-шники дают вам только название представления «ViewEmployeesInfo», которое возвращает все вышеуказанные данные (т.е. то что вы у них просили).

Вы теперь можете работать с данным представлением, как с обычной таблицей:

SELECT *
FROM ViewEmployeesInfo

Т.к. теперь все необходимые для отчета данные есть в одной «таблице» (а-ля вьюха), то вы с легкостью сможете переделать свой еженедельный отчет:

SELECT
  DepartmentName,
  COUNT(DISTINCT PositionID) PositionCount,
  COUNT(*) EmplCount,
  SUM(Salary) SalaryAmount,
  AVG(Salary) SalaryAvg
FROM ViewEmployeesInfo emp
GROUP BY DepartmentID,DepartmentName
ORDER BY DepartmentName

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

Т.е. для вас в данном случае, будто бы ничего и не поменялось, вы продолжаете так же работать с одной таблицей (только уже правильнее сказать с представлением ViewEmployeesInfo), которое возвращает все необходимые вам данные. Благодаря помощи ИТ-шников, детали по добыванию DepartmentName и PositionName остались для вас в черном ящике. Т.е. представление для вас выглядит так же, как и обычная таблица, считайте, что это расширенная версия таблицы Employees.

Давайте для примера еще сформируем ведомость, чтобы вы убедились, что все действительно так как я и говорил (что вся выборка идет из одного представления):

SELECT
  ID,
  Name,
  Salary
FROM ViewEmployeesInfo
WHERE Salary IS NOT NULL
  AND Salary>0
ORDER BY Name

Надеюсь, что данный запрос вам понятен.

Использование представлений в некоторых случаях, дает возможность значительно расширить границы пользователей, владеющих написанием базовых SELECT-запросов. В данном случае представление, представляет собой плоскую таблицу со всеми необходимыми пользователю данными (для тех, кто разбирается в OLAP, это можно сравнить с приближенным подобием OLAP-куба с фактами и измерениями).

Вырезка с википедии. Хотя SQL и задумывался как средство работы конечного пользователя, в конце концов он стал настолько сложным, что превратился в инструмент программиста.

Как видите, уважаемые пользователи, язык SQL изначально задумывался, как инструмент для вас. Так что, все в ваших руках и желании, не отпускайте руки.

HAVING – наложение условия выборки к сгруппированным данным

Собственно, если вы поняли, что такое группировка, то с HAVING ничего сложного нет. HAVING – чем-то подобен WHERE, только если WHERE-условие применяется к детальным данным, то HAVING-условие применяется к уже сгруппированным данным. По этой причине в условиях блока HAVING мы можем использовать либо выражения с полями, входящими в группировку, либо выражения, заключенные в агрегатные функции.

Рассмотрим пример:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary)>3000

Т.е. данный запрос вернул нам сгруппированные данные только по тем отделам, у которых сумма ЗП всех сотрудников превышает 3000, т.е. «SUM(Salary)>3000».

Т.е. здесь в первую очередь происходит группировка и вычисляются данные по всем отделам:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID -- 1. получаем сгруппированные данные по всем отделам

А уже к этим данным применяется условие указанно в блоке HAVING:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID -- 1. получаем сгруппированные данные по всем отделам
HAVING SUM(Salary)>3000 -- 2. условие для фильтрации сгруппированных данных

В HAVING-условии так же можно строить сложные условия используя операторы AND, OR и NOT:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary)>3000 AND COUNT(*)<2 -- и число людей меньше 2-х

Как можно здесь заметить агрегатная функция (см. «COUNT(*)») может быть указана только в блоке HAVING.

Соответственно мы можем отобразить только номер отдела, подпадающего под HAVING-условие:

SELECT
  DepartmentID
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary)>3000 AND COUNT(*)<2 -- и число людей меньше 2-х

Пример использования HAVING-условия по полю включенного в GROUP BY:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID -- 1. сделать группировку
HAVING DepartmentID=3 -- 2. наложить фильтр на результат группировки

Это только пример, т.к. в данном случае проверку логичнее было бы сделать через WHERE-условие:

SELECT
  DepartmentID,
  SUM(Salary) SalaryAmount
FROM Employees
WHERE DepartmentID=3 -- 1. провести фильтрацию детальных данных
GROUP BY DepartmentID -- 2. сделать группировку только по отобранным записям

Т.е. сначала отфильтровать сотрудников по отделу 3, и только потом сделать расчет.

Примечание. На самом деле, несмотря на то, что эти два запроса выглядят по-разному оптимизатор СУБД может выполнить их одинаково.

Думаю, на этом рассказ о HAVING-условиях можно окончить.

Подведем итоги

Сведем данные полученные во второй и третьей части и рассмотрим конкретное месторасположение каждой изученной нами конструкции и укажем порядок их выполнения:

Конечно же, вы так же можете применить к сгруппированным данным предложения DISTINCT и TOP, изученные во второй части.

Эти предложения в данном случае применятся к окончательному результату:

SELECT
  TOP 1 -- 6. применится в последнюю очередь
    SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary)>3000
ORDER BY DepartmentID -- 5. сортировка результата

SELECT
  DISTINCT -- показать только уникальные значения SalaryAmount
    SUM(Salary) SalaryAmount
FROM Employees
GROUP BY DepartmentID

Как получились данные результаты проанализируйте самостоятельно.

Заключение

Основная цель которую я ставил в данной части – раскрыть для вас суть агрегатных функций и группировок.

Если базовая конструкция позволяла нам получить необходимые детальные данные, то применение агрегатных функций и группировок к этим детальным данным, дало нам возможность получить по ним сводные данные. Так что, как видите здесь все важно, т.к. одно опирается на другое – без знания базовой конструкции мы не сможем, например, правильно отобрать данные, по которым нам нужно просчитать итоги.

Здесь я намеренно стараюсь показывать только основы, чтобы сосредоточить внимание начинающих на самых главных конструкциях и не перегружать их лишней информацией. Твердое понимание основных конструкций (о которых я еще продолжу рассказ в последующих частях) даст вам возможность решить практически любую задачу по выборке данных из РБД. Основные конструкции оператора SELECT применимы в таком же виде практически во всех СУБД (отличия в основном состоят в деталях, например, в реализации функций – для работы со строками, временем, и т.д.).

В последующем, твердое знание базы даст вам возможность самостоятельно легко изучить разные расширения языка SQL, такие как:

• GROUP BY ROLLUP(…), GROUP BY GROUPING SETS(…), …
• PIVOT, UNPIVOT
• и т.п.

В рамках данного учебника я решил не рассказывать об этих расширениях, т.к. и без их знания, владея только базовыми конструкциями языка SQL, вы сможете решать очень большой спектр задач. Расширения языка SQL по сути служат для решения какого-то определенного круга задач, т.е. позволяют решить задачу определенного класса более изящно (но не всегда эффективней в плане скорости или затраченных ресурсов).

Если вы делаете первые шаги в SQL, то сосредоточьтесь в первую очередь, именно на изучении базовых конструкций, т.к. владея базой, все остальное вам понять будет гораздо легче, и к тому же самостоятельно. Вам в первую очередь, как бы нужно объемно понять возможности языка SQL, т.е. какого рода операции он вообще позволяет совершить над данными. Донести до начинающих информацию в объемном виде – это еще одна из причин, почему я буду показывать только самые главные (железные) конструкции.

Удачи вам в изучении и понимании языка SQL.

Часть четвертая — habrahabr.ru/post/256045

Встроенные функции Transact-SQL — Клёвый код

Как проверить, существует ли ограничение в Sql server?

попробуйте это:

SELECT
    * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME ='FK_ChannelPlayerSkins_Channels'

— EDIT —

когда я первоначально ответил на этот вопрос, я думал «внешний ключ», потому что исходный вопрос задавался о поиске «FK_ChannelPlayerSkins_Channels». С тех пор многие люди прокомментировали нахождение других «ограничений» вот некоторые другие запросы для этого:

--Returns one row for each CHECK, UNIQUE, PRIMARY KEY, and/or FOREIGN KEY
SELECT * 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_NAME='XYZ'  


--Returns one row for each FOREIGN KEY constrain
SELECT * 
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME='XYZ'


--Returns one row for each CHECK constraint 
SELECT * 
    FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
    WHERE CONSTRAINT_NAME='XYZ'

вот альтернативный метод

--Returns 1 row for each CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and/or DEFAULT
SELECT 
    OBJECT_NAME(OBJECT_ID) AS NameofConstraint
        ,SCHEMA_NAME(schema_id) AS SchemaName
        ,OBJECT_NAME(parent_object_id) AS TableName
        ,type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
        AND OBJECT_NAME(OBJECT_ID)='XYZ'

Если вам нужно еще больше информации об ограничениях, загляните внутрь системная хранимая процедура master.sys.sp_helpconstraint чтобы узнать, как получить определенную информацию. Для просмотра исходного кода с помощью среды SQL Server Management Studio перейдите в раздел»Обозреватель объектов». Оттуда вы развернете базу данных «Master», затем развернете» программируемость», затем» хранимые процедуры», затем»Системные хранимые процедуры». Затем вы можете найти «sys.sp_helpconstraint» и щелкните его правой кнопкой мыши и выберите «Изменить». Просто будьте осторожны, чтобы не сохранить какие-либо изменения в нем. Кроме того, вы можете просто использовать хранимую процедуру на любом столе используя его как EXEC sp_helpconstraint YourTableNameHere.

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’

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если сотрудник получает фиксированную заработную плату
(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

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

В соединениях таблиц часто невозможно заранее предположить, всем ли значениям некоторого столбца из
одной таблице соответствует определённое значение из другой таблице. В случае несоответствия значение
является неопределённым (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

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

Реляционные базы данных и язык SQL

Функции SQL ISNULL (), NVL (), IFNULL () и COALESCE ()

Функции SQL IFNULL (), ISNULL (), COALESCE () и NVL ()

Посмотрите на следующую таблицу «Продукты»:

Предположим, что столбец «UnitsOnOrder» является необязательным и может содержать значения NULL.

Посмотрите на следующий оператор SELECT:

ВЫБЕРИТЕ ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
ИЗ продуктов;

В приведенном выше примере, если любое из значений «UnitsOnOrder» равно NULL, результат
будет NULL.

Решения

MySQL

Функция MySQL IFNULL () позволяет
вернуть альтернативное значение, если выражение NULL:

ВЫБЕРИТЕ ProductName, UnitPrice * (UnitsInStock + IFNULL (UnitsOnOrder, 0))
ИЗ продуктов;

или мы можем использовать функцию COALESCE (), например:

ВЫБЕРИТЕ ProductName, UnitPrice * (UnitsInStock + COALESCE (UnitsOnOrder, 0))
ИЗ продуктов;

SQL Server

Функция SQL Server ISNULL () позволяет
вы возвращаете альтернативное значение, когда выражение NULL:

ВЫБЕРИТЕ ProductName, UnitPrice * (UnitsInStock + ISNULL (UnitsOnOrder, 0))
ИЗ продуктов;

MS Access

Функция MS Access IsNull () возвращает
ИСТИНА (-1), если выражение является нулевым значением, иначе ЛОЖЬ (0):

ВЫБЕРИТЕ ProductName, UnitPrice * (UnitsInStock + IIF (IsNull (UnitsOnOrder), 0, UnitsOnOrder))
ИЗ продуктов;

Оракул

Функция Oracle NVL () дает тот же результат:

ВЫБЕРИТЕ ProductName, UnitPrice * (UnitsInStock + NVL (UnitsOnOrder, 0))
ИЗ продуктов;

Обработка NULL в T-SQL — CodeProject

Содержание

Всегда лучше выводить сообщение об ошибке, чем выводить неверный результат.Когда вы имеете дело с NULL , может быть вероятность получить вывод, даже если в коде есть некоторые логические ошибки. Он играет жизненно важную роль для поддержания качества и точности вывода базы данных.

В этой статье я хотел бы описать различные аспекты, общие представления и лучшие практики относительно NULL в T-SQL.

Ваши комментарии очень важны. :).

1. Пусто.
2. Пусто.
3. Это ноль.
4. Ничего особенного.
5. Отсутствует значение.
6. Это наименьшее значение.
7. Это незначительная величина.
8. Необязательное значение.
9. Недействителен.
10. Недействительна.

NULL означает «НЕТ РЕЗУЛЬТАТА» или «НЕИЗВЕСТНО» , который не равен самому себе.

 NULL <> NULL --NULL не равно NULL.if (NULL = NULL) - ничего не возвращает (ни ошибки, ни результата).
NULL + Anything = NULL - если вы добавляете что-нибудь, чтобы добавить, всегда возвращается NULL.
 

Правило 3 CODD: Систематическая обработка нулевых значений:

СУБД должна позволять каждому полю оставаться пустым (или пустым). В частности, он должен поддерживать представление «отсутствующей информации и неприменимой информации», которое является систематическим, отличным от всех обычных значений (например, «отличным от нуля или любого другого числа» в случае числовых значений) и независимым от данных. тип.Также подразумевается, что такие представления должны обрабатываться СУБД систематическим образом.

Microsoft

Значение NULL указывает, что значение неизвестно. Значение NULL отличается от пустого или нулевого значения. Никакие два значения null не равны. При сравнении двух значений NULL или между NULL и любым другим значением возвращается значение unknown, поскольку значение каждого NULL неизвестно.

Вики

SQL NULL — это термин, используемый для обозначения отсутствующего значения.Значение NULL в таблице — это значение в поле, которое кажется пустым. Поле со значением NULL — это поле без значения. Очень важно понимать, что значение NULL отличается от нулевого значения или поля, содержащего пробелы.

Функции COALESCE и ISNULL T-SQL используются для возврата первого не NULL выражения среди входных аргументов. Оба используются для обработки значения NULL в T-SQL. ISNULL принимает два аргумента, а COALESCE требует более двух аргументов.

 объявить @x int = null;
 Выберите ISNULL (@ x, '0') КАК ISNULL_OUTPUT
 ********************************************! *
  Выход: 
   ISNULL_OUTPUT
       0
 
 ********************************************! *
 

 объявить @x int = null;
 объявить @y int = null;
 объявить @z int = 20;
 COALESCE (@ x, @ y, @ z, '0') как COALESE_OUTPUT
 
  Выход: 
   COALESE_OUTPUT
       20
 
 
 
 

НУЛЛИФ

NULLIF принимает два аргумента и возвращает NULL , если аргументы равны NULL в противном случае возвращает первый аргумент.

 объявить @x int = 0;
выберите NULLIF (@ x, 0) в качестве результата

  Выход 
  Результат 
ЗНАЧЕНИЕ NULL
 

НУЛЕВО и НЕ НУЛЕНО

Невозможно проверить значения NULL с помощью операторов отношения, таких как =, <или <>. Чтобы проверить, является ли значение NULL или нет, нам нужно использовать оператор IS NULL или IS NOT NULL .

 объявить @value int = null;
если @value равно NULL
начать
  Выберите "Value is NULL" как результат
конец

установить @ значение = 1
если @value НЕ ПУСТО
начать
  В качестве результата выберите 'Value is not NULL'
конец
 

• NULL не равно никакому значению
• NULL не больше, меньше или отличается от значения
• NULL не равно самому NULL .
• NULL не больше, меньше или отличается от NULL

В И НЕ В

• IN не возвращает совпадение со значением, равным NULL
• NOT IN возвращает false , если хотя бы одно из значений NULL

 declare @Temp table (
Col1 int,
Col2 varchar (20)
  )
вставить в значения @Temp (1, 'Test 1')
вставить в значения @Temp (2, 'Test 2')
вставить в значения @Temp (3, 'Test 3')
вставить в значения @Temp (4, 'Test 4')
вставить в значения @Temp (5, 'Test 5')
вставить в значения @Temp (null, 'Test 6')
вставить в значения @Temp (7, 'Test 7')
вставить в значения @Temp (null, 'Test 8')
вставить в значения @Temp (null, 'Test 9')
вставить в значения @Temp (10, 'Test 10')

выберите * из @Temp, где Col1 в (1,3,6)


выберите * из @Temp, где Col1 в (1,3,6, null)


выберите * из @Temp, где Col1 не входит в (1,3,6, null)
 

МЕЖДУ И НЕ МЕЖДУ

• МЕЖДУ и НЕ МЕЖДУ возвращает false , если одно из значений ограничений NULL

 объявление @Temp table (
Col1 int,
Col2 varchar (20)
  )
вставить в значения @Temp (1, 'Test 1')
вставить в значения @Temp (2, 'Test 2')
вставить в значения @Temp (3, 'Test 3')
вставить в значения @Temp (4, 'Test 4')
вставить в значения @Temp (5, 'Test 5')
вставить в значения @Temp (null, 'Test 6')
вставить в значения @Temp (7, 'Test 7')
вставить в значения @Temp (null, 'Test 8')
вставить в значения @Temp (null, 'Test 9')
вставить в значения @Temp (10, 'Test 10')

выберите * из @Temp, где Col1 от 1 до 7

выберите * из @Temp, где Col1 от нуля до 7

выберите * из @Temp, где Col1 от 1 до нуля

выберите * из @Temp, где Col1 не между 1 и 7

выберите * из @Temp, где Col1 не между нулем и 7

выберите * из @Temp, где Col1 не между 1 и нулем
 

NULL в порядке (сортировка)

NULL — наименьшее значение в порядке сортировки.

NULL в группе по

NULL считаются равными при выполнении group by. Если столбец в предложении GROUP BY содержит строки с NULL , то они будут сгруппированы в одну группу.

NULL в агрегированных методах

Агрегатные функции — COUNT , SUM , AVG , MAX , MIN и LIST — не обрабатывают NULL .Есть одно исключение из этого правила: COUNT (*) возвращает количество всех строк, даже строк, все поля которых NULL . Но COUNT (FieldName) ведет себя так же, как и другие агрегатные функции, поскольку подсчитывает только строки, в которых указанное поле не является NULL .

История

• 04.10.2014: начальная версия

ISNULL vs COALESCE — выражения / функции в SQL Server

ISNULL и COALESCE с некоторыми общими функциями делает их эквивалентными, но некоторые функции заставляют их работать и вести себя по-другому, как показано ниже.

— Сходство
Оба могут использоваться для построения / создания списка CSV , как показано ниже:

ИСПОЛЬЗУЙТЕ [AdventureWorks]
ИДТИ

ОБЪЯВИТЬ @csv VARCHAR (2000)

ВЫБЕРИТЕ @csv = ISNULL (@csv + ',', '') + FirstName
ОТ Person.Contact
ГДЕ ContactID <= 10
ЗАКАЗАТЬ ПО ИМЕНИ

выберите @csv

установить @ csv = NULL
ВЫБЕРИТЕ @csv = COALESCE (@csv + ',', '') + FirstName
ОТ Person.Contact
ГДЕ ContactID <= 10
ЗАКАЗАТЬ ПО ИМЕНИ

выберите @csv
 

  Оба дадут одинаковый результат: 
Карла, Кэтрин, Фрэнсис, Густаво, Умберто, Джей, Ким, Маргарет, Пилар, Рональд 

- Разница № 1
ISNULL принимает только 2 параметра.Первый параметр проверяется на значение NULL, если он равен NULL, то возвращается второй параметр, в противном случае возвращается первый параметр.
COALESCE принимает два или более параметров. Можно применить 2 или столько же параметров, но он возвращает только первый параметр , отличный от NULL, , пример ниже.

DECLARE @ str1 VARCHAR (10), @ str2 VARCHAR (10)

- ISNULL () принимает только 2 аргумента
ВЫБРАТЬ ISNULL (@ str1, 'manoj') AS 'IS_NULL' - manoj

- COALESCE принимает несколько аргументов и возвращает первый аргумент, отличный от NULL.
ВЫБЕРИТЕ COALESCE (@ str1, @ str2, 'manoj') как 'COALESCE' - манодж

- ISNULL () эквивалент COALESCE, путем вложения ISNULL ()
ВЫБРАТЬ ISNULL (@ str1, ISNULL (@ str2, 'manoj')) AS 'IS_NULL eqv' - manoj
 

- Разница # 2
ISNULL не преобразует неявно тип данных, если типы данных обоих параметров различаются.
С другой стороны, COALESCE неявно преобразует тип данных параметров в порядке более высокого приоритета .

- ISNULL не выполняет неявное преобразование
выберите ISNULL (10, getdate ()) как 'IS_NULL' - ошибки отсутствуют
 

Сообщение об ошибке

:
Сообщение 257, уровень 16, состояние 3, строка 1
Неявное преобразование типа данных datetime в int недопустимо.
Используйте функцию CONVERT для выполнения этого запроса. 

- COALESCE выполняет неявное преобразование и преобразуется в тип данных с более высоким приоритетом.выберите COALESCE (10, getdate ()) как «COALESCE» - 1900-01-11 00: 00: 00.000, выводит 10, но преобразует его в datetime [datetime> int]
выберите COALESCE (getdate (), 10) как COALESCE - {Текущая дата} 2010-12-23 23: 36: 31.110
выберите COALESCE (10, 'Manoj') как 'COALESCE' - 10 [int> varchar]
выберите COALESCE ('Manoj', 10) как 'COALESCE' - выдает ошибку, он выполняет неявное преобразование, но не может изменить 'Manoj' на Integer.
 

Сообщение об ошибке

:
Сообщение 245, уровень 16, состояние 1, строка 1
Ошибка преобразования при преобразовании значения varchar Manoj в тип данных int.

- Разница № 3
Аналогично пункту ISNULL всегда возвращает значение с типом данных первого параметра.
В отличие от этого, COALESCE возвращает значение типа данных в соответствии с приоритетом и совместимостью типов данных.

ОБЪЯВИТЬ @str VARCHAR (5)

НАБОР @str = NULL

- ISNULL возвращает усеченное значение после его фиксированного размера, здесь 5
SELECT ISNULL (@str, 'Half Full') AS 'IS_NULL' - половина

- COALESCE возвращает полное значение длины, возвращает полную строку из 12 символов
ВЫБЕРИТЕ COALESCE (@str, 'Half Full') AS 'COALESCE' - наполовину заполнен
 

- Разница № 4
Согласно MS BOL, ISNULL и COALESCE, хотя и эквивалентны, могут вести себя по-разному.Выражение, включающее ISNULL с ненулевыми параметрами, считается NOT NULL , в то время как выражения, включающие COALESCE с ненулевыми параметрами, считаются NULL . Таким образом, для индексирования выражений, включающих COALESCE с ненулевыми параметрами, вычисляемый столбец может быть сохранен с использованием атрибута столбца PERSISTED .

- ISNULL () разрешен в вычисляемых столбцах с первичным ключом
СОЗДАТЬ ТАБЛИЦУ T1 (
col1 INT,
col2 AS ISNULL (col1, 1) PRIMARY KEY)

- COALESCE () не допускается в непостоянных вычисляемых столбцах с первичным ключом.
СОЗДАТЬ ТАБЛИЦУ T2 (
col1 INT,
col2 КАК COALESCE (col1, 1) ПЕРВИЧНЫЙ КЛЮЧ)
 

Сообщение об ошибке

:
Msg 1711, уровень 16, состояние 1, строка 1
Невозможно определить ограничение PRIMARY KEY для столбца «col2» в таблице «T2».Вычисляемый столбец должен быть постоянным и не допускать значения NULL.
Msg 1750, уровень 16, состояние 0, строка 1
Не удалось создать ограничение. См. Предыдущие ошибки. 

- COALESCE () допускается только как постоянные вычисляемые столбцы с первичным ключом
СОЗДАТЬ ТАБЛИЦУ T2 (
col1 INT,
col2 AS COALESCE (col1, 1) УСТОЙЧИВЫЙ ПЕРВИЧНЫЙ КЛЮЧ)

- Очистить
ТАБЛИЦА ПАДЕНИЯ T1
ТАБЛИЦА ПАДЕНИЯ T2
 

MSDN BOL ссылки:
ISNULL: http: // msdn.microsoft.com/en-us/library/ms184325.aspx
COALESCE: http://msdn.microsoft.com/en-us/library/ms1

.aspx
http: // code. msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE

Нравится:

Нравится Загрузка ...

Связанные

SQL IS NULL / IS NOT NULL

На языке SQL оператор IS позволяет фильтровать результаты, которые соответствуют значению NULL.Cet opérateur est незаменимый car la valeur NULL - это ценность inconnue и ne peut par conséquent pas être filter par les opérateurs de compareison (ср. Égal, inférieur, supérieur ou différent).

Синтаксис

Залейте в фильтр результатов результатов полей с колонками значение NULL, чтобы использовать удобный синтаксис:

 SELECT *
ОТ `table`
WHERE nom_colonne IS NULL 

Обратное, чтобы фильтровать результаты и получать уникальные записи, которые не имеют значения null, или удобнее использовать синтаксис suivante:

 SELECT *
ОТ `table`
WHERE nom_colonne IS NOT NULL 

Сохранение: l’opérateur IS retourne en réalité un booléen, c’est à dire une valeur TRUE, si la condition est vrai or FALSE si la condition n’est pas респект.Cet opérateur est souvent utilisé avec la condition WHERE mais peut aussi Trouve son utilité lorsqu’une sous-Requête est utilisée.

Exemple

Imaginons - это приложение, которое может использовать содержимое таблицы. Этот стол может иметь 2 колонны для ассоциированных адресов ливрейзона и фактического использования в качестве пользователя (grâce à une clé étrangère). Si cet utilisateur n’a pas d’adresse de facturation or de livraison, alors le champ reste à NULL.

Таблица «utilisateur»:

go

1 12237 9229

id	nom	date_inscription	fk_adresse_livraison_id	fk_adresse_facturation_id
0019 1 12237
24	Сара	2013-02-17	NULL	NULL
25	Anne	2013-02-21	13	14
26	Frédérique	02.03.2013	NULL	NULL

Пример 1: utilisateurs sans adresse de livraison

Il est possible d'obtenir la list des utilisateurs qui ne possible pas d'adresse de livraison en utilisant la Requête SQL suivante:

 SELECT *
ОТ ʻutilisateur`
ГДЕ `fk_adresse_livraison_id` ЕСТЬ NULL 

Résultat:

---

1

0 21

id	nom	date_inscription	fk_adresse_livraison_id	NULL
26	Frédérique	2013-03-02	NULL	NULL

Les enregistrements retournés montrent bien que seul les utilisateurs ayant la valeur 'NULL для чемпионов мира ливраизон.

Пример 2: утилизаторы с адресом доставки

Залить уникальные утилизаторы по адресу, по которому можно найти запрос SQL suivante:

 SELECT *
ОТ ʻutilisateur`
ГДЕ `fk_adresse_livraison_id` НЕ ЯВЛЯЕТСЯ NULL 

Результат:

_adresse_id

_dresse21

id	nom	date_inscription	fk_adresse_livraison_id							1 12	12
25	Anne	2013-02-21	13	14

Les lignes retournés sont exclusive celles qui n'ont pas une valeur NULL pour le champ de l ' adresse de livraison.

SQL не равен нулю и не является нулем Kullanımı - Web Tasarım & Programlama

NULL içerisinde herhangi bir değer bulundurmayan sütunlardır. Yani kayıt işlemi sırasında bir sütuna değer girilmezse o sütunun değeri NULL olarak adlandırılır. Sütuna boşluk girilirse sütun boş görülse dahi o sütun NULL olmaktan çıkacaktır. SQL de NULL değer içeren kayıtları sorgulamada karşılaştırma operatörü kullanılmaz. Eğer bir tabloda NULL değer içeren kayıtlar bulunmak isteniyorsa sorguda IS NULL ifadesi kullanılır.Aynı şekilde NULL değer içermeyen kayıtlar listelenmek isteniyorsa da IS NOT NULL ifadesi kullanılır.

imdi IS NULL ve IS NOT NULL kullanımı ile ilgili basit bir sorgu örneği gerçekleştirelim.

Örneimizde « ticaret» veritabanında bulunan « musteri » tablosunda telefon numarası girilmemiş müşterileri listeleyeceğiz.

Новый запрос ile yeni sorgu penceresi açarak sorgumuzu aşağıdaki gibi yazarak Execute dediğimizde sonuş aşağıdaki gibi olacaktır.Müşteri tablosunda hiç telefon kaydı girilmemiş müşterilerin bilgileri gelecektir.

Görüldüğü gibi sorgu çalıştırıldığında değer girilmemiş, Null olan kayıtların listelenmesi sağlanmıştır.

ВЫБРАТЬ * ИЗ musteri
ГДЕ tel IS NULL

ВЫБРАТЬ * ОТ musteri ГДЕ tel IS NULL

НЕ НУЖЕН kullanımıda НЕДЕЙСТВИТЕЛЬНО Kullanımı gibi olacaktır.« musteri » tablosunda phone numarası NULL olmayan kayıtları listelemek istersek sorgumuzu aşağıdaki gibi düzenlememiz yeterli olacaktır.

ВЫБРАТЬ * ИЗ musteri
ГДЕ tel НЕ ПУСТО

ВЫБРАТЬ * ИЗ musteri ГДЕ tel НЕ ПУСТО

Как получить совершенно неверные значения NULL в SQL Server

При ссылке на NULL в наших наборах данных мы часто используем такие фразы, как «значение NULL » или «значение NULL ." Я делаю это все время. Я постоянно это вижу. Такие фразы настолько распространены, что мы мало думаем об их использовании. Но в этой фразе заложено понятие NULL как фактического значения. Нет, это не так. NULL не является значением, несуществующим значением. Это не ноль. Это не пустая строка. Значение не может равняться NULL . Никакие два значения NULL не равны.

A NULL Значение часто определяется как неизвестное или неприменимое, но даже эти определения могут быть предметом обсуждения.Например, запись может не включать дату рождения покупателя, потому что продавец не спрашивал или потому, что покупатель не предоставил ее, но покупатель по-прежнему знает эту дату, поэтому она вряд ли неизвестна и не менее применима. Может быть много причин для отсутствия данных, и было много споров о точном значении NULL . Если вам нужно придать определенное значение NULL , то отсутствует или отсутствует данные , вероятно, ваша самая безопасная ставка.

Возможно, лучше рассматривать NULL как параметр или маркер, указывающий, не существует ли значение данных. Похоже, именно так SQL Server обрабатывает NULL . Механизм базы данных использует специальный битовый массив, чтобы отслеживать, какие столбцы в строке имеют значение NULL , а какие нет. Битовая карта содержит бит для каждого столбца, при этом бит установлен в 1, если столбец имеет значение NULL , то есть, если значение отсутствует.

Некоторых, особенно начинающих разработчиков T-SQL, смущает то, как SQL Server Management Studio указывает, что для определенного столбца не существует никакого значения: отображая то, что кажется значением, которое читается как NULL . Например, следующий оператор SELECT возвращает данные из таблицы Product в образце базы данных AdventureWorks2014 :

ВЫБЕРИТЕ Имя как ProductName, ProductLine, Class, Style, SellEndDate FROM Production.Продукт ГДЕ ИД ПРОДУКТА МЕЖДУ 680 И 715;

Как показывают следующие результаты, каждая строка включает по крайней мере один столбец, для которого не существует значения:

Название продукта	ProductLine	Класс	Стиль	Дата окончания продажи
HL Road Frame - черный, 58	R	H	U	NULL
HL Road Frame - красный, 58	R	H	U	NULL
Шлем Sport-100 красный	S	NULL	NULL	NULL
Шлем Sport-100, черный	S	NULL	NULL	NULL
Носки для горных велосипедов, M	м	NULL	U	29 мая 2012 г., 00:00:00.000
Носки для горных велосипедов, L	м	NULL	U	2012-05-29 00: 00: 00.000
Шлем Sport-100 синий	S	NULL	NULL	NULL
Крышка с логотипом AWC	S	NULL	U	NULL
Джерси с длинным рукавом и логотипом, S	S	NULL	U	NULL
Джерси с длинным рукавом и логотипом, M	S	NULL	U	NULL
Джерси с длинным рукавом и логотипом, L	S	NULL	U	NULL

Если мы посмотрим на столбцы со значениями NULL , мы увидим, насколько сложно определить, почему значения отсутствуют.Например, значение SellEndDate отсутствует, потому что продукт все еще работает или приложение не смогло должным образом записать дату окончания? Значения Class и Style отсутствуют, потому что они не применяются к этим конкретным строкам или потому, что кто-то небрежно ввел данные?

Еще большая проблема с просмотром результатов таким образом заключается в том, что он предполагает, что база данных фактически хранит несколько экземпляров этого загадочного заполнителя, называемого NULL , и что все эти значения NULL одинаковы.Они не то же самое. Там ничего нет. Ничто не может быть таким же, как ничто. Ничто нельзя сравнивать ни с чем. Вы можете рассматривать их как фундаментальные принципы NULL . Принципы небытия. А непонимание этих принципов может привести к разного рода проблемам при работе с SQL Server, что приведет ко многим из описанных здесь сбоев.

Разработчики, которые не понимают, как работает NULL , иногда используют операторы сравнения для сравнения выражения со значением NULL , что приводит к утверждению, которое выглядит примерно так:

ВЫБЕРИТЕ заголовок, имя, отчество, фамилию ОТ человека.Человек ГДЕ Заголовок = NULL И BusinessEntityID <7;

Здесь предполагается, что NULL является допустимым значением и, следовательно, может сравниваться с другими значениями NULL . Это означает, что в этом случае оператор должен возвращать каждую строку, значение Title которой равно NULL . Быстрая проверка исходных данных покажет четыре строки, которые соответствуют предполагаемой логике оператора.Однако, поскольку механизм базы данных не может ничего сравнивать с ничем, он не будет возвращать эти строки, а вместо этого предоставит нам пустой набор результатов.

Если мы предположим из пустого набора результатов, что ни одна строка в исходных данных не соответствует нашим критериям, мы могли бы удовлетвориться заявлением как есть и отправить код в производство. (Будем надеяться, что никто на самом деле этого не сделает.) Мы также можем продолжить эту логическую траекторию и переделать оператор, чтобы он возвращал данные, отличные от NULL :

ВЫБЕРИТЕ заголовок, имя, отчество, фамилию ОТ человека.Person WHERE Title <> NULL И BusinessEntityID <7;

Оператор больше не пытается сравнивать NULL с NULL , а скорее сравнивает реальные значения, отличные от NULL , с NULL , что могло бы показаться столь же надежным сравнением, если бы не тем более. Нет, это не так. Мы не можем сравнивать реальные ценности ни с чем больше, как мы не можем сравнивать ничего ни с чем.В результате инструкция по-прежнему не возвращает данных, хотя мы точно знаем, что данные существуют.

Но прежде чем мы пойдем дальше, обратите внимание, что есть случаи, когда сравнение значений NULL таким образом может работать, и мы рассмотрим следующий сбой. А пока знайте, что правильный способ выполнить сравнения NULL - использовать операторы IS NULL и IS NOT NULL .Например, следующий оператор SELECT использует оператор IS NULL для возврата строк, для которых значение Title равно NULL :

ВЫБЕРИТЕ Заголовок, Имя, Отчество, Фамилия ОТ Person.Person ГДЕ Заголовок равен NULL И BusinessEntityID <7;

На этот раз инструкция возвращает ожидаемые данные с отсутствующим значением Title и парой значений MiddleName :

Название	Имя	Отчество	Фамилия
NULL	Кен	Дж	Санчес
NULL	Терри	Ли	Даффи
NULL	Роберто	NULL	Тамбурелло
NULL	Граб	NULL	Уолтерс

Мы также можем вернуть те строки, которые содержат фактическое значение Title , вместо этого используя IS NOT NULL оператор:

ВЫБЕРИТЕ заголовок, имя, отчество, фамилию ОТ человека.Лицо , ГДЕ Заголовок НЕ ПУСТОЙ И BusinessEntityID <7;

Теперь инструкция возвращает только следующие две строки:

Название	Имя	Отчество	Фамилия
г-жа	Гейл	А	Эриксон
г.	Йосеф	H	Голдберг

Операторы IS NULL и IS NOT NULL операторы обеспечивают логику, необходимую для обработки неоднозначной природы значений NULL . Назначение этих операторов - определить, является ли выражение NULL и из этого вернуть либо TRUE , либо FALSE .Операторы сравнения, когда сталкиваются со значением NULL , вместо этого хотят вернуть UNKNOWN , что может привести к непредсказуемым и дорогостоящим ошибкам при внедрении в производственную среду.

В последней неудаче я продемонстрировал, почему вы не должны использовать операторы сравнения со значениями NULL . Даже если есть результаты, которые должны быть получены, утверждение, подобное следующему, скорее всего, не вернет их:

ВЫБЕРИТЕ заголовок, имя, отчество, фамилию ОТ человека.Человек ГДЕ Заголовок = NULL И BusinessEntityID <7;

Слово скорее всего здесь ключевое. Оказывается, описанное мной поведение является поведением по умолчанию, но не единственным. SQL Server поддерживает функцию под названием ANSI_NULLS , которая определяет, как ядро ​​базы данных обрабатывает сравнения NULL . Эта опция является переносом из призрака SQL Server Past и не рекомендуется с 2005 года.Но настройка все еще существует, она по-прежнему работает, и по-прежнему может доставлять всевозможные неприятности.

По умолчанию SQL Server устанавливается с включенной опцией ANSI_NULLS , что дает нам поведение, которое я описал до сих пор. Если мы сравниваем значения с NULL , ядро ​​базы данных не возвращает эти строки, даже если существует NULL .

Мы можем проверить текущую настройку ANSI_NULLS , запустив оператор, подобный следующему:

IF ((32 & @@ OPTIONS) = 32) PRINT 'Включена опция ANSI_NULLS.' ELSE PRINT' Опция ANSI_NULLS отключена. ';

Если никто не перепутал ANSI_NULLS , ядро ​​базы данных вернет первую опцию, которая даст нам результаты, которые мы видели до сих пор. Но предположим, что мы отключили опцию:

УСТАНОВИТЬ ANSI_NULLS OFF; ВЫБЕРИТЕ заголовок, имя, отчество, фамилию ОТ человека.Человек ГДЕ Заголовок = NULL И BusinessEntityID <7;

Теперь наш оператор возвращает ожидаемые четыре строки, несмотря на то, что мы используем оператор сравнения с NULL :

Название	Имя	Отчество	Фамилия
NULL	Кен	Дж	Санчес
NULL	Терри	Ли	Даффи
NULL	Роберто	NULL	Тамбурелло
NULL	Граб	NULL	Уолтерс

При выключенной опции мы также можем использовать оператор не равно:

УСТАНОВИТЬ ANSI_NULLS OFF; ВЫБЕРИТЕ заголовок, имя, отчество, фамилию ОТ человека.Person WHERE Title <> NULL И BusinessEntityID <7;

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

Название	Имя	Отчество	Фамилия
г-жа	Гейл	А	Эриксон
г.	Йосеф	H	Голдберг

Если мы снова включим эту опцию (это настройка по умолчанию), ядро ​​базы данных снова не вернет никаких строк:

УСТАНОВИТЬ ANSI_NULLS ON; ВЫБЕРИТЕ Заголовок, Имя, Отчество, Фамилия ОТ Person.Person WHERE Title = NULL И BusinessEntityID <7;

Поскольку опция ANSI_NULLS устарела, вы не должны использовать ее и должны обновить любой код, который это делает, при условии, что у вас есть доступ и время.Если вы этого не сделаете, вас могут ожидать более серьезные проблемы.

Представьте себе приложение, которое подключается к двум различным экземплярам SQL Server, один с включенной опцией (по умолчанию), а другой - с выключенной. Если в коде используются операторы сравнения со значениями NULL , вы можете получить неточные данные или противоречивые результаты. Даже если вы имеете дело только с одним экземпляром SQL Server, вы все равно рискуете получить неожиданные результаты, особенно если операторы включают опцию ANSI_NULLS на уровне сеанса.

Функция ISNULL имеет тенденцию вызывать небольшую путаницу при первом обнаружении, отчасти из-за ее имени, которое подразумевает значение, аналогичное оператору IS NULL . Другая причина путаницы возникает из-за того, что такие продукты, как Microsoft Access и MySQL, также включают функцию ISNULL , где она ведет себя совершенно иначе, как следует из названия. Но мы говорим о SQL Server и должны понимать, как здесь реализовано ISNULL .Недостаток понимания может привести к неправильному использованию и невозможности использовать его истинное предназначение в SQL Server.

Давайте рассмотрим несколько примеров, чтобы продемонстрировать, начнем с простого запроса, который возвращает продукты, для которых значение Color равно NULL :

ВЫБЕРИТЕ Имя КАК ProductName, Цвет ИЗ Production.Product ГДЕ ИД ПРОДУКТА МЕЖДУ 940 И 949 И Цвет НУЛЬ;

Название продукта	Цвет
LL Туринговые рули	NULL
Руль HL Touring	NULL

Если мы не знакомы с ISNULL в SQL Server или переходим из Access, мы можем попытаться получить те же результаты, используя ISNULL в нашем предложении WHERE :

ВЫБЕРИТЕ Имя КАК ProductName, Цвет ИЗ Производство.Продукт ГДЕ ИД ПРОДУКТА МЕЖДУ 940 И 949 И ISNULL (Цвет);

К счастью, эта конструкция сгенерирует следующую ошибку, которую мы, вероятно, поймаем сразу или, по крайней мере, на этапе вопросов и ответов:

Сообщение 174, уровень 15, состояние 1, строка 305 Для функции isnull требуется 2 аргумента (ов).

Те из нас, у кого есть опыт работы с MySQL, могут вместо этого использовать другой подход с функцией ISNULL , сравнив ее с 1 или 0, двумя возможными возвращаемыми значениями функции:

ВЫБЕРИТЕ Имя КАК ProductName, Цвет ИЗ Производство.Продукт ГДЕ ИД ПРОДУКТА МЕЖДУ 940 И 949 И ISNULL (Цвет) = 1);

Еще раз получаем сообщение об ошибке:

Сообщение 174, уровень 15, состояние 1, строка 322 Для функции isnull требуется 2 аргумента (ов). около ';'.

К настоящему времени мы должны были проверить документацию, чтобы проверить, что эта функция должна делать, и пришли к выводу, что на основе синтаксиса и довольно явных сообщений об ошибках мы должны добавить второй аргумент, который мы делать:

ВЫБЕРИТЕ Имя КАК ProductName, Цвет ИЗ Производство.Продукт ГДЕ ИДЕНТИФИКАЦИЯ ПРОДУКТА МЕЖДУ 940 И 949 И ISNULL (цвет, «нейтральный»);

К сожалению, все, что мы сделали, это сделали наш предикат совершенно нелогичным, что привело к еще одному (но другому) сообщению об ошибке:

Сообщение 4145, уровень 15, состояние 1, строка 339 Выражение небулевого типа, указанное в контексте, где ожидается условие, рядом с ';'.

Дело в том, что в SQL Server цель функции ISNULL - просто заменить значение выражения действительным значением, если исходное значение - NULL (несуществующее). Если бы мы хотели сделать предыдущий пример правильным, нам нужно было бы правильно определить наше логическое условие:

ВЫБЕРИТЕ Имя КАК ProductName, Цвет ИЗ Производство.Продукт ГДЕ ИД ПРОДУКТА МЕЖДУ 940 И 949 И ISNULL (цвет, «нейтральный») IN («черный», «нейтральный»);

Теперь оператор SELECT возвращает желаемые результаты, а не ошибку:

Название продукта	Цвет
LL Mountain Frame - черный, 40	Черный
LL Туринговые рули	NULL
Руль HL Touring	NULL
LL Шатуны	Черный

Тем не менее, мы можем добиться того же, используя IS NULL , без дополнительной обработки:

.

No related posts.