Decode Vs Case

DECODE OR CASE - You make a Case! Decode is a function that compares the expression to each search value one by one. if the search is found, then the query returns the corresponding result. if not match, then oracle returns default vault. if default is missing , then it returns null. Lets see how decode works? Are you ready?

--Lets create a table
CREATE TABLE prabinbaniya.NFL 
( conference_name VARCHAR2(5),
 team_name VARCHAR2(50),
 home_state CHAR(2)
);

--Insert Records
--NFC West Team Data
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_W','Arizona Cardinals','AZ');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_W','SF 49ers','CA');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_W','Seattle Seahawks','WA');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_W','St Louis Rams','MO');
COMMIT;

--NFC EAST Team Data
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_E','Dallas Cowboys','TX');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_E','New York Gaints','NY');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_E','Philadelphia Eagles','PA');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUEs ('NFC_E','Washington Redskins','WA');
COMMIT;

--NFC NORTH Team Data
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_N','Chicago Bears','IL');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_N','Detroit Lions','MI');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_N','Green Bay Packers','WI');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUEs ('NFC_N','Minnesota Vikings','MN');
COMMIT;

--NFC SOUTH Team Data
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_S','Atlanta Falcons','GA');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUEs ('NFC_S','Carolina Panthers','NC');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUES ('NFC_S','New Orleans Saints','LA');
INSERT INTO prabinbaniya.NFL (conference_name, team_name, home_state) VALUEs ('NFC_S','Tampa Bay Buccaneers','FL');
COMMIT;

SELECT conference_name, count(*)
FROM prabinbaniya.nfl
GROUP BY conference_name;

Output:

CONFERENCE_NAME COUNT(*)
---------------   --------
NFC_W                   4
NFC_E                    4
NFC_S                    4
NFC_N                   4

SELECT decode (conference_name,
       'NFC_W','NFC WEST',
       'NFC_E','NFC EAST',
       'NFC_S','NFC SOUTH',
       'NFC_N','NFC NORTH',
       'UNKNOWN') "CONFERENCE NAME",
       count(*)
FROM      prabinbaniya.nfl
GROUP BY    conference_name;

OUTPUT:

CONFERENCE NAME       COUNT(*)
--------------------------------   --------
NFC WEST                               4
NFC EAST                                4
NFC SOUTH                             4
NFC NORTH                             4
 
Syntax:

DECODE(expr, search, result
             [, search, result ]...
       [, DEFAULT ]
      );

CASE can do everything decode can do plus more. It can be used with logical operators, predicates and searchable queries. it can be used in SQL and in PLSQL whereas decode is limited to SQL only. Not to mention CASE works faster too. Lets convert the above decode query to use CASE.

 SELECT
  CASE conference_name
   WHEN 'NFC_W'
   then 'NFC WEST'
   when 'NFC_E' 
   then 'NFC EAST'
   when 'NFC_N' 
   then 'NFC NORTH'
   when 'NFC_S' 
   then 'NFC SOUTH'
   ELSE 'UNKNOWN'
  END
    FROM prabinbaniya.nfl;

Case Syntax

CASE { simple_case_expression
     | searched_case_expression
     }
     [ else_clause ]
     END

Again, Choice is yours! Can you always make a CASE to use DECODE function? I can always make a case to use a case function.