SQL Server COALESCEThe COALESCE() function in SQL Server evaluates the arguments in sequence and gives the first NON-NULL value in a specified number of expressions. If it evaluates all the list values as null or as not found any non-null value, it returns NULL. SyntaxThe following are the syntax that illustrates the COALESCE() function: Parameter ExplanationThis function accepts only one parameter, which is the list that has various values. value1, value2,…..,valueN: It specifies the values of the list to return the NON-NULL or NULL value in the output. It can be of any type but should be the same for all expressions. We can understand it more clearly through the following illustration: CASE1: COALESCE(NULL, NULL); CASE2: COALESCE(0, NULL); Here we can see that the function can accept a number of arguments and return the first non-null value. In a case when all the values of the list are null, this function returns NULL. Therefore, CASE1 and CASE2 always return NULL because they cannot find any non-null value. SQL Server Coalesce ExampleLet us understand the COALESCE() function with multiple examples. It is noted that we can use the COALESCE() function with the SELECT statement directly. 1. COALESCE function with expression as character string data This example uses the COALESCE function with multiple values and returns the string 'Hello' because it is the first non-null expression: After execution, we will see the below output: 2. COALESCE function with expression as a numeric value This example uses the COALESCE function with multiple values to evaluate a list of arguments and return the first number, which is non-null: After execution, we will see the below output: 3. COALESCE function always evaluates an integer first This example uses the COALESCE function with value as an integer followed by character expression and returns an integer as an output: After execution, we will see the below output: If we change the order of the integer and string (first string and then integer), SQL Server will through an error. See the below output: 4. COALESCE function with tables In this example, we will see how to coalesce() function works with the table. First, we will create a table named 'emp_contacts' using the below statements: Next, we will add data into this table as below: We can verify the table using the SELECT statement as below: Now, we will use the Coalesce function to select the columns homenumber, worknumber, and personalnumber. If the columns have NULL values, it will return the value 'NA' (not applicable). See the below code: After execution, we will see the below output: COALESCE and CASE ExpressionThe COALESCE function is used as a syntactic shortcut for the CASE expression. The query optimizer will write COALESCE(expressions) in the CASE expression as below: Let us take a previous example of Coalesce function that selects the columns homenumber, worknumber, and personalnumber from the emp_contact table. If the columns have NULL values, it will return the value 'NA' (not applicable). We can write this coalesce function in CASE expression as follows: After execution, we will get the same output as the one that uses the coalesce function: COALESCE vs. ISNULLWe will often be confused to distinguish between the ISNULL function and COALESCE expression because both have a similar purpose but can behave differently. Some of the key differences discussed below:
The #DempTable1 cannot be created because the PRIMARY KEY cannot allow NULL values, and the nullability of the COALESCE expression for column2 can return NULL. The #DempTable2 can be created because the ISNULL function's nullability evaluates to NOT NULL.
Next TopicSQL Server IF ELSE
|