Что такое coalesce в sql

Функции COALESCE и ISNULL в T-SQL – особенности и основные отличия

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

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

COALESCE

COALESCE – функция T-SQL, которая возвращает первое выражение из списка параметров, неравное NULL.

Синтаксис

COALESCE (входящие параметры [,…n])

Особенности

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

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

ISNULL

ISNULL – функция T-SQL, которая заменяет значение NULL первого параметра, на указанное значение во втором параметре. Другими словами, в случае если первый параметр равен NULL, то возвращается второй параметр.

Синтаксис

ISNULL (check_expression, replacement_value)

Особенности

Тип данных выражения replacement_value должен явно преобразовываться к типу данных значения check_expresssion, в итоге у функции ISNULL тип возвращаемого значения равен типу данных check_expression.

Пример

Сравнение COALESCE и ISNULL — их основные отличия

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

Примеры, показывающие отличия в работе функций COALESCE и ISNULL

Пример 1. Отличие в количестве параметров.

Пример 2. Отличие в возвращаемом типе данных (текстовые данные).

Пример 3. Отличие в возвращаемом типе данных (целочисленный тип данных).

Пример 4. Отличие в возвращаемом значении, т.е. результат ISNULL — это не NULL, а результат COALESCE — это NULL.

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

Рекомендации по использованию функций COALESCE и ISNULL

Функцию COALESCE рекомендовано использовать тогда, когда просто необходимо вернуть первое значение отличное от NULL.

Функцию ISNULL используйте для замены значения NULL, при возможном его появлении в выражении, на другое значение, при этом в качестве второго параметра лучше указывать выражение 100% отличное от NULL, например константу. Другими словами, чтобы предотвратить ситуацию, когда мы хотим использовать значение, а оно у нас NULL.

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

Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.

Источник

COALESCE (Transact-SQL)

Transact-SQL Syntax Conventions

Syntax

Arguments

expression
Is an expression of any type.

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Return Types

Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.

Remarks

Comparing COALESCE and CASE

The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE (expression1,. n) is rewritten by the query optimizer as the following CASE expression:

As such, the input values (expression1, expression2, expressionN, and so on) are evaluated multiple times. A value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. This result is in compliance with the SQL standard. In either case, different results can be returned between the first evaluation and upcoming evaluations.

For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. As a result, you can get different results depending on the isolation level of the query. For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. As an alternative, you can rewrite the query to push the subquery into a subselect as shown in the following example:

Comparing COALESCE and ISNULL

The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

Because ISNULL is a function, it’s evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.

Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

ISNULL takes only two parameters. By contrast COALESCE takes a variable number of parameters.

Examples

A. Running a simple example

The following example shows how COALESCE selects the data from the first column that has a nonnull value. This example uses the AdventureWorks2012 database.

B. Running a complex example

Here is the result set.

C: Simple Example

The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. Assume for this example that the Products table contains this data:

We then run the following COALESCE query:

Here is the result set.

D: Complex Example

The following example uses COALESCE to compare the values in three columns and return only the non-null value found in the columns.

Источник

COALESCE (Transact-SQL)

Синтаксические обозначения в Transact-SQL

Синтаксис

Аргументы

expression
Выражение любого типа данных.

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Типы возвращаемых данных

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

Remarks

Сравнение COALESCE и CASE

Поэтому входные значения (expression1, expression2, expressionN и т. д.) вычисляются многократно. Выражение значения, содержащее вложенный запрос, считается недетерминированным, и вложенный запрос вычисляется дважды. Этот результат соответствует стандарту SQL. В любом случае могут быть возвращены различные результаты для первого и последующих вычислений.

Сравнение COALESCE и ISNULL

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

Так как ISNULL — это функция, она вычисляется только один раз. Как было сказано выше, входные значения для выражения COALESCE могут вычисляться несколько раз.

Различается определение типа данных результирующего выражения. ISNULL использует тип данных первого параметра, COALESCE следует правилам выражения CASE и возвращает тип данных значения с самым высоким приоритетом.

Проверки для ISNULL и COALESCE также различаются. Например, значение NULL для ISNULL преобразуется в значение int, а для COALESCE необходимо предоставить тип данных.

ISNULL принимает только два параметра. А COALESCE принимает переменное количество параметров.

Примеры

A. Выполнение простого примера

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

Б. Выполнение сложного примера

В. Простой пример

В приведенном ниже примере показано, как COALESCE выбирает данные из первого столбца, содержащего значение, отличное от NULL. В этом примере предполагается, что таблица Products содержит следующие данные:

Затем выполняется следующий запрос COALESCE:

Г. Сложный пример

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Что такое coalesce в sql

Использование функций Coalesce(), ISNULL() и NVL():

Если Вы взгляните на вышеупомянутый результирующий набор, то заметите, что везде, где значение TEST_COL1 есть NULL, запрос возвратил значение TEST_COL2. Для Informix и MYSQL оба столбца имеют значения NULL, поэтому было возвращено NULL-значение. Теперь давайте выполним подобный оператор с использованием COALESCE. С помощью COALESCE мы хотим получить такие данные: если значение TEST_COL1 есть NULL, то должно быть возвращено значение столбца TEST_COL2, а если значение TEST_COL2 также является NULL, то возвращаться должна константа 9999.

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

После выполнения этот запрос также возвратит то же самое значение, что и оператор с COALESCE. Последний скрывает сложность выражения CASE, когда имеется более 3-х аргументов. Следует помнить один момент, а именно, когда используется COALESCE, все выражения должны иметь один и тот же тип данных или же они должны быть неявно конвертируемыми к одному и тому же типу данных, в противном случае возникает ошибка.

Пожалуйста, не забывайте что, если Вы используете функции на индексируемых столбцах в критериях отбора или в условиях соединения, то индекс использоваться не будет. Есть способы заставить его работать (использование FBI в Oracle, вычисляемые столбцы в SQL Server или столбцы генерации выражения в DB2 LUW), если Вам действительно необходимо это сделать

Другие различия, о которых Вы должны знать:

— Другая важная вещь, когда Вы используете ISNULL(), заключается в том, что эта функция оценивает первое значение, и значение второго параметра автоматически ограничивается этой длиной; COALESCE() не имеет этого ограничения. Вот пример (синтаксис T-SQL):

Функция ISNULL() возвращает ‘A’, в то время как coalesce вернет ‘ABCD’. Нужно иметь это в виду, иначе вы получите неожиданные результаты.

— И точно так же, как в посте UNION/UNION ALL, в котором мы рассуждали о неявных преобразованиях типа данных, приводящих к проблемам, неявные преобразования типа данных могут создать головную боль и здесь. В случае функции COALESCE(), если значения имеют различные типы данных, Вы можете получить как ошибки, так и неверные результаты. Пример:

В результате получаем ошибку: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘test’ to data type int.
(преобразование значение varchar ‘test’ к тапу данных int вызывает ошибку)

даст:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
(ошибка арифметического переполнения при преобразовании выражения к типу данных datetime)

вернет неверные результаты в результате неявного преобразования типа. Вы получите: «1900-04-11 00:00:00.000» вместо 100.

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

Источник