What is difference between DECODE and CASE - SQL Interview Questions

Hi friends, I am going to share you one more important question from SQL Interview point of view. You will be asked this question in every SQL Developer interviews. Either they will ask you difference between DECODE and CASE or what are the advantages of using CASE over DECODE. So let us discuss on this and prepare ourselves for this important question.

• CASE is a statement while DECODE is a function. 
• CASE can work with logical operators other than '=' DECODE performs an equality check only. 
• CASE is capable of other logical comparisons such as < ,> ,BETWEEN , LIKE etc

Advantage of CASE over DECODE

CASE is a statement and DECODE is a function We can use the CASE in the where clause and can not use the DECODE in the where clause. 

DECODE can check equality operators only where as CASE can support all relational operators DECODE can be used in sql only where as CASE can be used in SQL AND PL/SQL CASE is better than DECODE.

Which is faster decode or case?

From performance perspective, In Oracle decode and CASE does not make any difference. But in Exadata , Decode is faster than CASE. The Decode operation is done at storage Server level where the data is present BUT CASE is done at DB Instance level which receives data from DB storage Level.

Why decode is used in SQL?

In Oracle, DECODE function allows us to add procedural if-then-else logic to the query. DECODE compares the expression to each search value one by one. If expression is equal to a search, then the corresponding result is returned by the Oracle Database. If a match is not found, then default is returned.

What is CASE function in SQL?

The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By and Group By clause. In Case statement, we defined conditions. Once a condition is satisfied, its corresponding value is returned.

What is decode function in Oracle?

DECODE compares expressions to each search value one by one. If expression is equal to a search , then Oracle Database returns the corresponding result . If no match is found, then Oracle returns default . If default is omitted, then Oracle returns null.

Syntax of Case and Decode

CASE

SELECT contact_id,
CASE
  WHEN employee_id = 1 THEN 'Ritesh'
  WHEN employee_id = 2 THEN 'Yash'
  ELSE 'Null'
END
FROM contacts;

DECODE Syntax

SELECT employee_name,
DECODE(employee_id, 10000, 'Permanent',
                    10001, 'Permanent',
                    10002, 'Permanent',
                    'Contract') Employee_type
FROM employee;

In above example, if employee_id is 10000,10001,10002 employee type is permanent, else type will be contract. By default it is set up as contract.

The above DECODE statement is equivalent to the following IF-THEN-ELSE statement:

IF employee_id = 10000 THEN
   employee_type := 'Permanent';

ELSIF employee_id = 10001 THEN
   employee_type := 'Permanent';

ELSIF employee_id = 10002 THEN
   employee_type := 'Permanent';

ELSE
   employee_type := 'Contract';

END IF;

So guys this was the details from my side for above important question. I hope you will be able to answer the question in interview. Next topic will be 'Difference Between Analytical and aggregate function. I hope you have liked the information. Please subscribe the blog.

Read this also : Difference Between ROWNUM and ROWID in ORACLE SQL