• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Web development » mysql » MySQL COALESCE and NULLIF Function

MySQL COALESCE and NULLIF Function

October 8, 2018 by techgoeasy Leave a Comment


We will be discussing MySQL COALESCE and NULLIF Function  in this post . We will also be showing  MySQL COALESCE  examples and NULLIF mysql Function example

COALESCE Function

COALESCE  in MySQL is a very useful function to select first not null values from the list of values

Syntax

COALESCE(val1, val2,val3,…valn)

The above syntax is equivalent to the following IF-THEN-ELSE statement

IF val1 is not NULL THEN val1
ELSIF val2 is not NULL THEN val2
ELSIF val3 is not NULL THEN val3
ELSE NULL;
END IF

This is also equivalent to case expression

Case
when val1 is not NULL then val1
when val2 is not NULL then val2
when val3 is not NULL then val3
else NULL
end

Usage

mysql> select COALESCE(1,2,3,null);
+———————-+
| COALESCE(1,2,3,null) |
+———————-+
| 1 |
+———————-+
1 row in set (0.00 sec)
mysql> select COALESCE(null,null,’a’,’b’,null);
+———————————-+
| COALESCE(null,null,’a’,’b’,null) |
+———————————-+
| a |
+———————————-+
1 row in set (0.00 sec)

mysql> select COALESCE(null,null,null);
+————————–+
| COALESCE(null,null,null) |
+————————–+
| NULL |
+————————–+
1 row in set (0.00 sec)

Suppose you have a table EMP which contains employee mobile_number,office_number and home_number. It may contains null value for any of these field. Now you want to select the phone in priority mobile_number > office_number > home_number in-case of null values. So we can use below coalesce statement

select emp_name, coalesce(mobile_number,office_number,home_number) contact_no from emp;

NULLIF function

Nullif Function in MySQL is a very useful function to compare two values and give null in case they are same, give val1 in case the they are not same

NULLIF(val1,val2)

Usage

mysql> select nullif(1,2);
+————-+
| nullif(1,2) |
+————-+
| 1 |
+————-+
1 row in set (0.00 sec)

mysql> select nullif(2,1);
+————-+
| nullif(2,1) |
+————-+
| 2 |
+————-+
1 row in set (0.00 sec)

mysql> select nullif(1,1);
+————-+
| nullif(1,1) |
+————-+
| NULL |
+————-+
1 row in set (0.00 sec)


Filed Under: mysql Tagged With: MySQL COALESCE, NULLIF Function

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • Oracle Indexes and types of indexes in oracle with example
  • Top 30 Most Useful Concurrent Manager Queries
  • Top 46 Oracle ASM Interview Questions
  • Oracle dba interview questions and answers
  • 40 question you must know about R12.2



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Multi Language Support in 11i/R12
  • EBS PDB service name disappear from listener in 19c
  • how to download oracle e-business suite 12.2 software
  • Step by step upgrade process to R12.2 Upgrade part -2(Main Upgrade Driver for R12.2.0)
  • Step by step upgrade process for R12.2 Upgrade Part -1

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us