COALESCE() Function SQL

COALESCE() is another of those functions dealing with NULL. It takes a list of parameters and returns the first one that is not NULL. This can be handy in that the parameters do not necessarily need to be column data. For example if we wished to turn NULL data into zeros in a query we could do this:

SELECT COALESCE(column1,etc,0) FROM table

So if the data in column1 were not NULL it would be returned. If not column1, then column2. If none of the previous data was not NULL then the default would be zero. For example, if we had a table of fictional works and wished to list them and their authors, but we knew that the author column was NULL in the case of unknown authors, we could submit the following query:

SELECT book_name, COALESCE(author,"anon") FROM fict_books

This works similarly to ISNULL(), NVL() and IFNULL() with the added benefit that it can check more than one column.

< CASE Functions SQL | CONCAT Function SQL >


Send notes in disappearing ink!

Interesting Pages