MySQL COALESCE Function: Definition, Syntax, and Examples

The COALESCE() function in MySQL returns the first non-NULL value from a list of values. It is a helpful function when working with nullable fields and ensures meaningful output instead of NULL.

COALESCE() Example

students Table

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	Delhi	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

The following SQL statement used to display the preferred contact information (Email first, then Phone if Email is NULL).

Example

SELECT name,

COALESCE(email, phone_number, 'No Contact Available') AS preferred_contact

FROM students;

Try it yourself

Output:

name	preferred_contact
Alice	1234567890
Bob	bob@email.com
Charlie	No Contact Available
David	david@email.com
Eve	No Contact Available
Frank	No Contact Available
Alice	No Contact Available

Explanation:

If email is NULL, it uses phone_number.
If both are NULL, it returns "No Contact Available".

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.