Decode in SQL

Syntax of DECODE

Here's the basic syntax:

DECODE(expression , search , result [, search , result]... [, default])

Expression: This is the value to compare.

Search: This is the value that is compared against the expression.

Result: This is the value returned, if the expression is equal to the search.

Default: This is optional. If no matches are found, the DECODE function will return default. If default is omitted, then the function will return NULL.

How DECODE() Function works in SQL?

Let's illustrate this with an example. Suppose we have a table named "Employees" with the following data:

Emp_ID Emp_Name Department
1 John IT
2 Emma HR
3 Adam Sales
4 Lisa IT

Now, if we want to decode the 'Department' column and get a numeric code for each department, we can use DECODE like this:

SELECT Emp_Name, DECODE(Department, 'IT', 1, 'HR', 2, 'Sales', 3, 0) AS Dept_Code FROM Employee;

This will output:

Emp_Name Dept_Code
John 1
Emma 2
Adam 3
Lisa 1

Examples of SQL DECODE()

Let's try to understand the SQL decode better. Below are examples for better understanding. In this, we have an emp table. 

Example 1

Consider we have a sales table: 

select person_name, decode(city, 'Ahmedabad', '1', 'Noida', 2, 'Delhi', 3, 'Indore', 4,0) as city_code from sales;

This will output:

PERSON_NAME           City_Code
-----------------------------------------
Neha                       1
Jay                        2
Aayush                     3
Priya                      4
Naman                      2

Example 2 

Consider another example in which we want to put the sales amount :

select city, decode(salesid, 1, 2000, 2, 3000, 3, 9000, 4, 7000, 5, 4000) as salesAmount from sales;

This will output

CITY                           SALESAMOUNT
------------------------------ -----------
Ahmedabad                             2000
Noida                                 3000
Delhi                                 3000
Indore                                7000
Noida                                 4000
Delhi                                 3000

Example 3

Let's look at another example in which we want to display the salesperson's department. 

select person_name, decode(salesid, 1, 'Groceries', 2, 'Household appliances', 3, 'Vegetables', 4, 'Electronics', 5, 'Clothing') as department from sales;

 This will output

PERSON_NAME DEPARTMENT
---------- --------------------
Neha         Groceries
Jay          Household appliances
Aayush       Household appliances
Priya        Electronics
Naman        Clothing
Neha         Household appliances

Frequently Asked Questions

How to use decode in Oracle SQL Developer?

The decode function in Oracle SQL developer is used to replace the complex case statements. It works the same as it works in SQL. You provide an expression and some values, and then the matched expression gets printed out. 

Can DECODE be used with non-numeric data?

Yes, DECODE can be used with non-numeric data. It can compare strings, dates, or other data types.

Can we use nested DECODE?

Yes, DECODE can be nested to evaluate more complex conditions.

Is DECODE function ANSI SQL standard?

No, DECODE is not ANSI SQL standard. It is specific to Oracle SQL.

Conclusion

DECODE in SQL is a powerful tool that can simplify your SQL queries by providing conditional logic directly in your queries. However, DECODE is specific to Oracle SQL and not part of the ANSI SQL standard. It's always important to understand the specific tools and functions available in your SQL variant to write efficient and effective queries. Keep coding and exploring!

Here are some more related articles:

• LIMIT in SQL
• MySQL: Case Statement 
• MySQL Introduction
• MySQL Features

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSA, Competitive Programming, System Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning!!