What is COALESCE in SQL?
COALESCE is an SQL function which is specifically used to return the very first NOT NULL value from the list of values or expressions provided within the braces ( ) .
- COALESCE processes the values in the sequence they are provided.
- Values/Expressions within the COALESCE braces must be comma-separated.
COALESCE(value1, value2, value3, ...... , valueN)
COALESCE(column1, column2, column3, ...... , columnN)
A simple example would be to display the very first NOT NULL value from a list of hard-coded values.
SELECT COALESCE(NULL, NULL, 'Apple','Banana');
Of course, the answer is ‘Apple’. Let’s look at a little complex example.
An Employee can optionally maintain 3 Phone Numbers. You are asked to display the Employee’s Phone Number to Contact. You must display an available Phone Number in the order.
- If Primary is available, display it
- If Primary is NOT available and Secondary is available, display Secondary
- If both Primary and Secondary are NOT available, display Other
- If None are available, display “Not Available”
Let’s create a simple Employees table with Phone Number to demonstrate COALESCE in SQL Server.
Run the below Scripts, Create the table and Populate the records.
SQLFiddle for the Scripts:
CREATE TABLE EMPLOYEES ( Id INT, EMP_NAME VARCHAR(35), PH_PRIMARY VARCHAR(10), PH_SECONDARY VARCHAR(10), PH_OTHER VARCHAR(10) ) INSERT INTO EMPLOYEES SELECT 1001, 'Angel', '9876543210', '0987612345', '9878656721' UNION ALL SELECT 1002, 'Williams', NULL AS PH_PRIMARY, '0987612345', NULL AS PH_OTHER UNION ALL SELECT 1003, 'Mitchell', NULL AS PH_PRIMARY, NULL AS PH_SECONDARY, '9878656721' UNION ALL SELECT 1004, 'Kathy' , NULL AS PH_PRIMARY, NULL AS PH_SECONDARY, NULL AS PH_OTHER COMMIT SELECT * FROM EMPLOYEES
Now, lets use COALESCE function to achieve the Result.
SELECT ID, EMP_NAME, COALESCE(PH_PRIMARY, PH_SECONDARY, PH_OTHER, 'Not Available') as Contact FROM EMPLOYEES
- Values or Expressions used in COALESCE must be of same data-types to achieve a stable result
- However, you still can pass a Numeric/Integer and an Character columns. But, if the Numeric/Integer field is resulting first, it wouldn’t fail.
- In case, a Character field is identified, it will thrown an error as there exists a NOT NULL field with Numeric/Integer value in it. Yes, weird it is.
COLAESCE versus ISNULL
- COLLASCE function is capable of processing more than 1 Values or Expressions.
- Whereas, ISNULL function can take and validate only one Value.
COLAESCE versus CASE
- COALESCE is short-and-sweet format of CASE statement.
- COALESCE is a function whereas CASE is a Statement.
- Not to forget that the Database does internally convert the COALESCE to a CASE before processing.
- Below is the equivalent Query for CASE from the Example 2 stated above.
The results would look alike.
SELECT ID, EMP_NAME, CASE WHEN PH_PRIMARY IS NOT NULL THEN PH_PRIMARY WHEN PH_SECONDARY IS NOT NULL THEN PH_SECONDARY WHEN PH_OTHER IS NOT NULL THEN PH_OTHER ELSE 'Not Available' END as Contact FROM EMPLOYEES
Thank you for reading.
Please visit https://www.sql-scripts.com for anything that is related SQL Queries, Scripts and How to stuffs.