Brave Search

stackoverflow.com › questions › 17104329 › how-to-use-coalesce-in-mysql

How come I saw queries that returns multiple values? isnt it only the first not null value that is returned?

Yes, it is only the first non-NULL value that is returned. You must be mistaken about the queries you have seen where you thought that was not the case: if you could show us an example, we might be able to help clarify the misunderstanding.
And how do it decide which column to base? coalesce(column1,column2)? what if first column is null and other column is not null?

In order of its arguments: in this example, column1 before column2.
Or if im wrong or my syntax is wrong, how do i properly write it?

You're not wrong.

Can someone provide a very good and simple example on how to use it?

Taken from the documentation:

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL
``

And when it is desirable to use.

It is desirable to use whenever one wishes to select the first non-NULL value from a list.

Answer from eggyal on Stack Overflow

W3Schools

w3schools.com › sql › func_mysql_coalesce.asp

MySQL COALESCE() Function

❮ Previous ❮ MySQL Functions Next ❯ · Return the first non-null value in a list: SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com'); Try it Yourself » · The COALESCE() function returns the first non-null value in a list.

Stack Overflow

stackoverflow.com › questions › 17104329 › how-to-use-coalesce-in-mysql

How to use Coalesce in MySQL - Stack Overflow

Top answer

1 of 5

How come I saw queries that returns multiple values? isnt it only the first not null value that is returned?

Yes, it is only the first non-NULL value that is returned. You must be mistaken about the queries you have seen where you thought that was not the case: if you could show us an example, we might be able to help clarify the misunderstanding.
And how do it decide which column to base? coalesce(column1,column2)? what if first column is null and other column is not null?

In order of its arguments: in this example, column1 before column2.
Or if im wrong or my syntax is wrong, how do i properly write it?

You're not wrong.

Can someone provide a very good and simple example on how to use it?

Taken from the documentation:

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL
``

And when it is desirable to use.

It is desirable to use whenever one wishes to select the first non-NULL value from a list.

2 of 5

I personally use coalesce when I want to find the first column that isn't blank in a row from a priority list.

Say for example I want to get a phone number from a customer table and they have 3 columns for phone numbers named mobile, home and work, but I only want to retrieve the first number that isn't blank.

In this instance, I have the priority of mobile, then home and then work.

TABLE STRUCTURE
--------------------------------------------
| id | customername | mobile | home | work |
--------------------------------------------
| 1  | Joe          | 123    | 456  | 789  |
--------------------------------------------
| 2  | Jane         |        | 654  | 987  |
--------------------------------------------
| 3  | John         |        |      | 321  |
--------------------------------------------

SELECT id, customername, COALESCE(mobile, home, work) AS phone FROM customers

RESULT
------------------------------
| id | customername | phone  |
------------------------------
| 1  | Joe          | 123    |
------------------------------
| 2  | Jane         | 654    |
------------------------------
| 3  | John         | 321    |
------------------------------

Videos

02:57

YouTube

How to Use the COALESCE Function in SQL - YouTube

June 4, 2023

165