Top 5 usages of Decode in SQL

Decode in SQL is an Oracle Function that can be used as an if-then-else statement in SQL. This function is named DECODE.

This function allows us to add an if-then-else procedural logic to our SQL statements. DECODE statement is a very powerful function and many complex logics can be handled easily with this. Let’s get a complete understanding of this function and how it can be used.

But if you are in hurry to get the syntax it is

Select Decode( X, Y ,true, false) from dual;

What is the DECODE function in SQL ?

As I mentioned in the introduction, In Oracle, SQL DECODE is an inbuilt function that can be used for comparisons. As in if the condition of equality matches it returns some value else it returns a different value.

Look at the below example

DECODE(X, 1, 'Equal to one')

In the above example if X =1 the value returned will be “Equal to One”. Note that this does not have an else condition so if X != 1 it will return NULL.

Also X can be of any data type (number, varchar etc).

Now let’s see an example of DECODE function with Else condition

DECODE(X, 1, 'Equal to one','Not Equal to One')

Now the above SQL statement will not only check the value of X =1 but also if X != 1 then It will return ‘Not Equal to One’ from the Else Condition

Using Decode to handle multiple Conditions

If for example below is the Pseudocode of a if then else statement with multiple conditions , how this will be handled by DECODE.

Let’s suppose the stock data is stored in a table STOCKS

IF stock = 'ORCL' THEN
     stock_name := 'ORACLE';
ELSIF stock = 'HLL' THEN
     stock_name := 'UNILEVER';
ELSIF stock = 'RIL' THEN
   stock_name := 'RELIANCE INDUSTRIES';
ELSE  stock_name := 'Not Defined';

We will write the DECODE Statement as follows.

                     , 'HLL', 'UNILEVER'
                      , 'RIL', 'RELIANCE INDUSTRIES'
                      , 'Not Defined')

The above Select Statement will check each row for the value of STOCK and return the STOCK name as per the table which stores the conditional relation between stock and stock name.

Using Decode to check less than Greater than

In Oracle SQL DECODE function can also be used to check Greater than and less than. Let see an example where we use the SIGN function with DECODE.

To check 3 – 5 = less , 7-5 = greater , 5 and 5 as Equal

select decode (sign(5-5),0,’equal’,-1,’less’,1,’greater’) Equality_Check from dual
Decode in SQL
Equality check with DECODE
image 2 Decode in SQL 1
Greater than check with DECODE
image 3 Decode in SQL 2
Less than check with DECODE

Similarly, we can use decode to compare two dates (date1 and date2), where,

if date1 > date2, it should return date2. Else, the DECODE function should return date1

Select DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1) from dual

Hope you got a clear idea of how the DECODE function works in Oracle SQL. SQL is getting better with every release with different analytical function . IF you are looking for SQL training you can check out below resources

Become an Oracle SQL Specialist

The Complete Oracle SQL Bootcamp (2022)

$13.00 $109.99 in stock
Last update was on: May 26, 2022 7:29 am

Become an In-demand SQL Professional & Pass Oracle SQL 1Z0-071 (OCA) Certification Exam Confidently!


Is DECODE procedure or function?

DECODE is a Function that returns a value based on the condition.

Can DECODE be used inside the Anonymous block in PL SQL?


Why is DECODE mostly used?

It is used to check conditional expressions inside PL SQL programs.

You May Like

We will be happy to hear your thoughts

      Leave a reply

      Cloud Technologies Blog
      Enable registration in settings - general
      Compare items
      • Total (0)
      Shopping cart