SQL Case Statement in Oracle

Oracle SQL CASE statement gives you the flexibility to use sql IF ELSE logic in a SELECT, WHERE and JOIN clause.

Select CASE SQL

For example, let us assume we need to count the number of employees whose salary is less than 2000, between 2000 & 4000 and above 4000. The below query helps counting such distinct values from a single column itself

SELECT 
 COUNT (CASE WHEN salary < 2000 THEN 1 ELSE null END) count1,
 COUNT (CASE WHEN salary BETWEEN 2001 AND 4000 THEN 1 ELSE null END) count2,
 COUNT (CASE WHEN salary > 4000 THEN 1 ELSE null END) count3
 FROM employees;

    COUNT1     COUNT2     COUNT3
---------- ---------- ----------
         0         43         64

With the output we can see that

• There are 0 employees whose salary is less than 2000

• There are 43 employees whose salary is above 2000 and below 4000

• There are 64 employees whose salary is grater than 4000

SQL Select CASE Example 1

For each customer in the sample customers table, the following sql query lists the credit limit as Low if it equals $100, High if it equals $5000, and Medium if it equals anything else

SELECT cust_last_name,
   CASE credit_limit WHEN 100 THEN 'Low'
   WHEN 5000 THEN 'High'
   ELSE 'Medium' END
   FROM customers;

CUST_LAST_NAME       CASECR
-------------------- ------
...
Bogart               Medium
Nolte                Medium
Loren                Medium
Gueney               Medium

SQL Select CASE Example 2

We would like to output the name of the department for the corresponding department number. For all the departments from 10 to 40 have their department name values listed, the ELSE is used to deal with any other depart number other than listed (10 to 40)

SELECT ename, empno, deptno
  (CASE deptno
     WHEN 10 THEN 'Accounting'
     WHEN 20 THEN 'Research'
     WHEN 30 THEN 'Sales'
     WHEN 40 THEN 'Operations'
     ELSE 'Unknown'
   END) department
FROM emp
ORDER BY ename;

SQL Select CASE Example 3

The following sql query finds the average salary of the employees in the sample table employees, using $2000 as the lowest salary possible

SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary
   ELSE 2000 END) "Average Salary" FROM employees e;

Average Salary
--------------
    6461.68224