SQL coalesce
Created By: chatGPT
COALESCE is a function in SQL that allows you to return the first non-null value from a list of expressions. It is particularly useful when dealing with optional data, as it enables you to substitute null values with meaningful defaults. Here is the standard syntax:
COALESCE(expression1, expression2, ..., expressionN)In this syntax, you can provide multiple expressions. The COALESCE function evaluates them in order and returns the first value that is not null. If all values are null, it returns null.
SELECT COALESCE(column1, column2, 'default value') AS result
FROM table_name;Consider an example where you want to display a user's last name, but sometimes it's missing. Instead of showing nothing, you might want to display 'Unknown'. Here is how you can use COALESCE:
SELECT COALESCE(last_name, 'Unknown') AS display_name
FROM users;You can also use COALESCE with numeric values or other data types. For instance, if you're summing values and want to ensure that nulls are treated as zeros, you can do it like this:
SELECT COALESCE(sales_amount, 0) + COALESCE(discount, 0) AS total
FROM sales;COALESCE is not limited to two fields; you can use it with any number of fields. This gives it great flexibility when dealing with optional data, ensuring that you always have a fallback option when encountering null values.
SELECT COALESCE(column1, column2, column3, 'fallback value') AS result
FROM table_name;