Understanding the SQL DECODE() Function

How to Use the SQL DECODE() Function

The SQL DECODE() function is natively supported in the Oracle database and is available in the PL/SQL dialect. The DECODE() function allows the implementation of conditional logic within the query. This technique is important as it helps in data transformation when retrieving records from the database.

Consider the employees table below, where the department_id has numerical values. Instead, we want department_id to be called department_name and have more understandable categories.

Example table to transform using SQL DECODE() function. Image by Author.

The example below shows how to use the DECODE() function to transform the department_id into the actual names.

-- Using DECODE to transform department_id to department_name
SELECT 
    employee_id,
    first_name,
    last_name,
    DECODE(department_id, 
        1, 'IT', 
        2, 'HR', 
        3, 'Finance', 
        4, 'Marketing', 
        5, 'Sales', 
        'Unknown') AS department_name,
    salary,
    hire_date,
    city
FROM employees;

Example output table after using the SQL DECODE() function. Image by Author.

Understanding the SQL DECODE() Function

The DECODE() function is available in the Oracle database and allows you to transform a column using conditional logic. The DECODE() function allows for multiple conditional transformations using one simple query.

The syntax of the DECODE() function is as shown below:

DECODE(expression, search1, result1, search2, result2, ..., default)

Where:

• expression: The value to compare.

• search: The value to compare against the expression.

• result: The value returned if the expression matches the search value.

• default: The value returned if no match is found (optional).

Advanced Techniques and Considerations with the SQL DECODE() Function

Some advanced SQL operations can be performed using the DECODE() function. The advanced techniques allow for more complex conditional logic transformation. 

DECODE() with aggregate functions

The DECODE() function in Oracle can be used in the SELECT statement to recategorize a variable, and it can also be used in the SELECT statement with aggregate functions for a more complicated grouping, which further improves flexibility and interpretation of the results. 

In the following query, we use DECODE() to transform department_id into department_name. We also use DECODE() to categorize average salary into ‘High Paying’ or 'Low Paying'.

SELECT 
    department_id,
    
    -- Use DECODE to transform department_id into department_name
    DECODE(
        department_id,
        1, 'IT', 
        2, 'HR', 
        3, 'Finance', 
        4, 'Marketing', 
        5, 'Sales', 
        'Unknown'
    ) AS department_name,
    
    SUM(salary) AS total_salary, 
    
    -- Calculate the average salary for each department and round to nearest integer
    ROUND(AVG(salary), 0) AS average_salary,
    
    -- Use DECODE to categorize average salary into 'High Paying' or 'Low Paying'
    DECODE(
        SIGN(AVG(salary) - 65000), 
        1, 'High Paying', 
        0, 'High Paying', 
        -1, 'Low Paying' 
    ) AS salary_category
FROM 
    employees
GROUP BY 
    department_id; 

Example table output after using multiple SQL DECODE() functions. Image by Author

Nested DECODE() statements

Implementing the DECODE() function with subqueries allows you to perform sophisticated conditional logic transformations to the data. The nested DECODE() statement in Oracle helps when we want to evaluate multiple conditions in a table.

In the example below, we have used the nested DECODE() statement to categorize the employees based on their department and salary.

-- Select the columns
SELECT 
    employee_id,
    first_name, 
    last_name, 
    department_id,
    salary, 
    
    -- Use DECODE to categorize salary based on department and salary thresholds
    DECODE(department_id,
        
        1, DECODE( -- If department_id is 1 (IT)
            CASE 
                WHEN salary > 65000 THEN 'High' 
                ELSE 'Low' 
            END, 
            'High', 'IT High Salary', 
            'Low', 'IT Low Salary' 
        ),
        
        2, DECODE( -- If department_id is 2 (HR)
            CASE 
                WHEN salary > 55000 THEN 'High'
                ELSE 'Low' 
            END, 
            'High', 'HR High Salary', 
            'Low', 'HR Low Salary' 
        ),
        
        3, DECODE( -- If department_id is 3 (Finance)
            CASE 
                WHEN salary > 70000 THEN 'High' 
                ELSE 'Low' 
            END, 
            'High', 'Finance High Salary', 
            'Low', 'Finance Low Salary' 
        ),
        
        4, DECODE( -- If department_id is 4 (Marketing)
            CASE 
                WHEN salary > 68000 THEN 'High'
                ELSE 'Low' 
            END, 
            'High', 'Marketing High Salary', 
            'Low', 'Marketing Low Salary' 
        ),
        
        5, DECODE( -- If department_id is 5 (Sales)
            CASE 
                WHEN salary > 60000 THEN 'High' 
                ELSE 'Low'  
            END, 
            'High', 'Sales High Salary', 
            'Low', 'Sales Low Salary'
        ),
        
        'Unknown Department' -- Default value if department_id does not match any case
    ) AS salary_category
FROM 
    employees;

Example table output after using the nested DECODE() function. Image by Author

How DECODE() handles NULL values

The DECODE() function also handles NULL values differently in two ways. For one thing, DECODE() acts as though two NULL values are equivalent. This functionality means that NULL values are considered a match when comparing the column values to NULL. Still, you should watch out for unexpected results where the NULL is not explicitly accounted for, as it may mask the NULL values during transformation.

Also, you should know that, just like the CASE WHEN statement, the DECODE() function returns a NULL value if no match is found.

The example below shows how the DECODE() function returns null values where department_id and salary are missing.

-- Select columns
SELECT 
    employee_id,
    first_name,
    last_name, 
    department_id, 
    salary, 
    -- Determine the salary category based on the salary value
    DECODE(salary, 
        NULL, 'No Salary Information',
        60000, 'Standard Salary',
        'Other Salary') AS salary_category, 
    -- Determine the department name based on the department ID
    DECODE(department_id, 
        1, 'IT',
        2, 'HR',
        3, 'Finance',
        4, 'Marketing',
        5, 'Sales') AS department_name 
        -- If department_id does not match any of the above, return NULL
FROM 
    employees;

Example table output after using DECODE() to handle NULL values. Image by Author

DECODE vs. CASE WHEN in Oracle

The DECODE() function was the first to be introduced, and even though it has been superseded by the CASE WHEN statement, it is still preferred in some contexts. For example, the DECODE() function syntax is simpler, so if the logic is fairly simple, it is easier to read and understand. 

Also, the DECODE() function is usually faster since its performance was optimized as a built-in function, although the difference might be negligible. Finally, in legacy Oracle systems, you might have to use the DECODE() function as the built-in function. 

Alternatives to DECODE() in Other Databases

The DECODE() function is only supported in the Oracle database. However, SQL Server, PostgreSQL, and MySQL provide alternative methods for applying conditional data transformation logic.

The DECODE() function is useful in data transformation since it simplifies complex logic by eliminating the use of IF ELSE or CASE statements. The DECODE() function also improves query readability, allowing efficient data manipulation. Let us examine the different use cases of the DECODE() function.

Using CASE WHEN in SQL Server, PostgreSQL, and MySQL

The CASE WHEN statement provides an alternative to the DECODE() function for performing SQL's conditional logic transformation. The CASE WHEN syntax and implementation are consistent across SQL Server, PostgreSQL, and MySQL databases.

The example below shows how to use the CASE WHEN statement to transform data within the SELECT statement. The query also shows how to combine the CASE WHEN statement with other SQL functions for complex conditional transformation.

 -- Select department ID
SELECT 
    department_id,
    
    -- Use CASE to transform department_id into department_name
    CASE 
        WHEN department_id = 1 THEN 'IT'
        WHEN department_id = 2 THEN 'HR'
        WHEN department_id = 3 THEN 'Finance'
        WHEN department_id = 4 THEN 'Marketing'
        WHEN department_id = 5 THEN 'Sales'
        ELSE 'Unknown' 
    END AS department_name,
    
    SUM(salary) AS total_salary,
    
    -- Calculate the average salary for each department and round to the nearest whole number
    ROUND(AVG(salary), 0) AS average_salary,
    
    -- Use CASE to categorize average salary into 'High Paying' or 'Low Paying'
    CASE 
        WHEN AVG(salary) > 65000 THEN 'High Paying'
        ELSE 'Low Paying' 
    END AS salary_category
FROM 
    employees
GROUP BY 
    department_id; -- Group results by department ID   

Conclusion and Further Learning

Understanding the Oracle DECODE() function is important when learning how to transform data effectively. The DECODE() function offers various use cases for applying conditional logic during data transformation. It is also important that you learn alternative conditional transformation in SQL Server, PostgreSQL, and MySQL databases. I encourage you to practice the DECODE() function using different datasets and use cases to perfect your data transformation skills.

If you are looking to advance your data analysis skills, I recommend taking DataCamp’s Reporting in SQL course to build on your analysis and presentation skills. Similarly, you should check out our Associate Data Analyst in SQL career track to help you keep the progress of the necessary SQL you will need in your career as a data analyst. Finally, I highly recommend taking DataCamp’s Data-Driven Decision Making in SQL project to showcase your mastery in using different SQL techniques for analysis and reporting to stand out among other data analysts.