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
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
SELECT DECODE(X, 1, 'Equal to one') FROM dual;
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
SELECT DECODE(X, 1, 'Equal to one','Not Equal to One') FROM dual;
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'; END IF;
We will write the DECODE Statement as follows.
SELECT DECODE ( STOCK , 'ORCL', 'ORACLE' , 'HLL', 'UNILEVER' , 'RIL', 'RELIANCE INDUSTRIES' , 'Not Defined') From STOCKS;
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
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
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