• Arun Kumar

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.


Note: same CASE statement is used in PL/SQL blocks.

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. 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


27 views

Recent Posts

See All

Oracle Joins

When you normalize data and create tables inside database, the data is scattered into different tables. In order to create meaningful data, we must query data by joining multiple tables. This is where

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback