DECODE vs CASE IN ORACLE

While working on one of the projects got a chance to explore DECODE and CASE STATEMENT in Oracle Database.

By Unsplash

DECODE: Decode is an function in sql.It's a way of converting a written code into understandable language.

CASE: Case are the statement in sql.It's a way of responding to the occurrence of a value or what action to be performed when a particular value occurs.

DIFFERENCES

SYNTAX

Consider an table called employee with some data.

Example: Write an SQL to fetch id, name and if is_new is 1 then “‘NEW EMPLOYEE’ else ‘OLD EMPLOYEE’.

Solution:

Using CASE STATEMENT

select id , name ,
case
when is_new=1 then ‘NEW EMPLOYEE’
when is_new=2 then ‘OLD EMPLOYEE’
END as is_new
from employee;

CASE STATEMENT

Using DECODE

select id , name,
DECODE(is_new, 1, ‘NEW EMPLOYEE’, 2 , ‘OLD EMPLOYEE’) as is_new
from employee;

Thanks for the read🙂. Do clap if you find it useful👏.

“Keep learning and keep sharing knowledge”