• 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 » Oracle » Oracle Sql » Coalesce Function in Oracle

Coalesce Function in Oracle

April 10, 2020 by techgoeasy Leave a Comment


Coalesce Function is a useful function in oracle and it is very useful when we want to return first non null values in the list.This SQL tutorial provides explanations, examples for Coalesce Function in Oracle and Difference between Coalesce & NVL

Coalesce Function in Oracle

expr1 : This is returned if it is not null
expr2: This is returned if it is not null and first expression is null
exprn: This is returned if the preceding expression are null

So Coalesce in oracle will return first expression if it is not null else it will do the coalesce the rest of the expression

Important point

(1)Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

(2)COALESCE in oracle is equivalent to Case function

So COALESCE (expr1, expr2) is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1

ELSE expr2 END

Similarly,

COALESCE (expr1, expr2, …, exprn), for n>=3 is equivalent to:

CASE WHEN expr1 IS NOT NULL THEN expr1
WHEN expr2 IS NOT NULL THEN expr2

WHEN expr3 IS NOT NULL THEN expr3

WHEN exprn IS NOT NULL THEN exprn

END

Or this can be written as

CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE (expr2, ..., exprn) END

(3) Usually all the expr of the coalesce statement should be of the same datatype else ORA-00932 will be returned.

SQL> select coalesce('a',1,2) from dual;
select coalesce('a',1,2) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

If all occurrences of expression are numeric datatype or any non-numeric datatype that can be implicitly converted to a numeric datatype, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

Usage

SQL> select coalesce(1,2,3) from dual;
COALESCE(1,2,3)
---------------
1

SQL> SELECT COALESCE(null, null, null, 'X', 'Y');
COALESCE(...)
---------------
X

Coalesce Examples

Suppose we have a employee oracle table consisting address1, address2 and address3  and we can want to print the first not-null address of the employees from the table in order of priority address1 > address2 > address3

select emp_name, COALESCE(address1,address2,address3) from employee;

Difference Between Coalesce and NVL function

1.Coalesce is generalization of NVL function. NVL function can be used for two expressions ,while coalesce can be used for Many
2.In case of two expression ,they are same but implementation is different.Oracle evaluates both the expression in case of NVL while it just evaluate the first expression in case of coalesce.So if the second expression has errors,NVL will throw error while coalesce will not.
3.Coalesce is ANSI standard while NVL is Oracle specific

Related Articles

Oracle Sql tutorial
NULLIF function in Oracle
NVL function in Oracle
Oracle LISTAGG Function
https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm#SQLRF00617


Filed Under: Oracle, Oracle Sql

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

  • 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
  • Step by step upgrade process for R12.2 Upgrade Part -4(Applying 12.2.x Release Update Pack)

Copyright © 2021 : TechGoEasy

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