SQL DECODE Function Explained

1 year ago
Isabella Edwards
1 minute

In SQL, the DECODE function is used to compare an expression with a series of possible values and return the matching result. The syntax of the DECODE function is as follows:

DECODE(expr, search1, result1, search2, result2, ..., default_result)

expr is the expression to be compared, search1, search2, and so on are possible values, result1, result2, and so on are the corresponding results. If expr matches search1, return result1, if expr matches search2, return result2, and so on. If there is no matching value, return default_result.

For example, the following SQL query uses the DECODE function to return different bonuses based on the employee’s department.

SELECT employee_id, DECODE(department_id, 
                            10, salary * 0.1,
                            20, salary * 0.15,
                            30, salary * 0.2,
                            salary * 0.05) AS bonus
FROM employees;

In the example above, different bonus percentages will be returned based on the employee’s department being 10, 20, or 30; otherwise, the default bonus percentage will be returned.

bannerAds