There is an equivalent Oracle function for the ANSI CASE function – it is called DECODE in Oracle. Both of them operate on data that has been retrieved from the database and consequently there is a need to reduce the number of rows that are to be retrieved with the help of the WHERE clause. As for any post-processing action, this would add cycles to any SQL query where either one of these statements are used in.

The other difference between the two is that CASE is an ANSI SQL standard and is portable across the different database vendors.

The following are equivalent examples of the CASE and DECODE functions based on the Oracle sample HR schema.

CASE:

SELECT country_name, CASE region_id WHEN 1 THEN ‘EUROPE’ WHEN 2 THEN ‘SOUTH AMERICA’ WHEN 3 THEN ‘AUSTRALIA’ ELSE ‘UNKNOWN’ END CONTINENT
FROM   countries; 

DECODE:

SELECT country_name, Decode(region_id, 1,’EUROPE’, 2,’SOUTH AMERICA’, 3,’AUSTRALIA’, ‘OTHER’) CONTINENT
FROM   countries;