Coalesce in Oracle

Coalesce in Oracle | In this post, we can see how to use the coalesce function in Oracle which helps us to avoid null arguments.

What is Coalesce?
Coalesce is the function in Oracle that contains the list of arguments and it returns the first one which estimates a non-null value.

Syntax of the coalesce function in Oracle:-
Coalesce(e1, e2, ………en)
It returns the non-null expression in the list. And it requires at least 2 expressions.

Example of Coalesce in Oracle

Let us see some examples of coalesce function in Oracle. The query returns 1 because it is the first non-null argument.

SELECT coalesce(NULL, 1) FROM dual;

Output:

COALESCE(NULL,1)
------------------   
    1

But if we pass only null arguments to the coalesce() function then it will return null only. Here is an example, which returns null because it contains all null arguments.

SELECT Coalesce(NULL, NULL) FROM  dual;

Output:

C
-

The Coalesce() function returns a value of the data type if all the arguments have the same data type. If in case the conversion results fail then Oracle issues an error. Let us see an example that uses the arguments of different types for the coalesce() function.

SELECT Coalesce(NULL, 1, 'A') FROM dual;

Output:-

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

Output resulted in an error because the coalesce () function tried to convert the character data type of the third argument to a numeric type of the second argument.

Now let’s see what happens when coalesce function is used between two tables. Let’s create tables namely: Employee, Schedule, and subject tables.

CREATE TABLE employees
  (
     empno  NUMBER(4),
     ename  VARCHAR2(8),
     job    VARCHAR2(8),
     mgr    NUMBER(4),
     bdate  DATE,
     msal   NUMBER(6, 2),
     comm   NUMBER(6, 2),
     deptno NUMBER(2)
  ); 

The table is created and now let us insert some records into the table.

INSERT INTO employees VALUES 
(1, 'John', 'TRAINER', 2,
 DATE '1990-12-18', 800, NULL, 10);

INSERT INTO employees VALUES
(2, 'Jack', 'SALESREP', 3,
 DATE '1999-11-19', 1600, 300, 10);

INSERT INTO employees VALUES
(3, 'Carlson', 'SALESREP', 4,
 DATE '1998-10-21', 1700, 500, 20);

INSERT INTO employees VALUES
(4, 'Scarlet', 'MANAGER', 5,
 DATE '2000-09-22', 1800, NULL, 20);

INSERT INTO employees VALUES 
(5, 'Joseph', 'SALESREP', 6,
 DATE '1997-08-23', 1900, 1400, 30);

INSERT INTO employees VALUES
(6, 'Richard', 'MANAGER', 7,
 DATE '1987-07-24', 2000, NULL, 30);

All the records are inserted and now let us create the schedule table.

CREATE TABLE schedule
  (
     subject   VARCHAR2(6),
     begindate DATE,
     trainer   NUMBER(4),
     location  VARCHAR2(20)
  );  

The schedule table is created now let’s insert some records into the table.

INSERT INTO schedule VALUES
('SQL', DATE '1999-04-12', 1, 'Newyork' );

INSERT INTO schedule VALUES
('OAU', DATE '1999-08-10', NULL, 'Australia');

INSERT INTO schedule VALUES
('SQL', DATE '1999-10-04', 3, 'Europe ');

INSERT INTO schedule VALUES
('SQL', DATE '1999-12-13', 4, 'Singapore' );

INSERT INTO schedule VALUES
('JAV', DATE '1999-12-13', 5, 'Canada');

INSERT INTO schedule VALUES
('XML', DATE '2000-02-03', NULL, 'Belgium' );

INSERT INTO schedule VALUES
('JAV', DATE '2000-02-01', 7, 'Bhutan' );

INSERT INTO schedule VALUES
('PLS', DATE '2000-09-11', NULL, 'srilanka' ); 

Lets us create another table namely the subject.

CREATE TABLE subject
  (
     code        VARCHAR2(6),
     description VARCHAR2(30),
     category    CHAR(3),
     duration    NUMBER(2)
  );

Let us insert some records now into the subject table.

INSERT INTO subject VALUES
('SQL', 'SQL course', 'GEN', 4);

INSERT INTO subject VALUES
('OAU', 'Oracle course', 'GEN', 1);

INSERT INTO subject VALUES
('JAV', 'Java course', 'BLD', 4);

INSERT INTO subject VALUES
('PLS', 'PL/SQL course', 'BLD', 1);

INSERT INTO subject VALUES
('XML', 'XML course', 'BLD', 2);

INSERT INTO subject VALUES
('ERM', 'ERM course', 'DSG', 3);

INSERT INTO subject VALUES
('PMT', 'UML course', 'DSG', 1);

INSERT INTO subject VALUES
('RSD', 'C# course', 'DSG', 2);

INSERT INTO subject VALUES
('PRO', 'C++ course', 'DSG', 5);

INSERT INTO subject VALUES
('GEN', 'GWT course', 'DSG', 4); 

Now let us join all three tables, Using the coalesce function.

SELECT DISTINCT 
s.code, o.begindate, s.duration,
CASE
  WHEN o.trainer IS NOT NULL THEN e.ename
  ELSE NULL
END AS trainer
FROM employees e, subject s, schedule o
WHERE Coalesce(o.trainer, -1) IN (e.empno, -1) 
AND o.subject = s.code; 

Let’s see the output:-

 CODE	BEGINDATE	DURATION	TRAINER 
-------------------------------------------------
  PLS	11-SEP-00	   1	               
  JAV	13-DEC-99	   4	        Joseph
  XML	03-FEB-00	   2	             
  SQL	13-DEC-99	   4	        Scarlet
  OAU	10-AUG-99	   1	            
  SQL	04-OCT-99	   4	        Carlson
  SQL	12-APR-99	   4	        John

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search