Case Statement

Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.

Syntax:

CASE  [ expression ]

  WHEN condition_1 THEN result_1

  WHEN condition_2 THEN result_2

  ...

  WHEN condition_n THEN result_n

  ELSE result

END

expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further.

result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.

Note:

If no condition is found to be true, then the case statement will return the value in the ELSE clause.

If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL.

You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.

Applies To:

Oracle 9i, Oracle 10g, Oracle 11g

Example:

You could use the case statement in an SQL statement as follows: (includes the expression clause)

select table_name,

CASE owner

  WHEN 'SYS' THEN 'The owner is SYS'

  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'

  ELSE 'The owner is another value'

END

from all_tables;

Or you could write the SQL statement using the case statement like this: (omits the expression clause)

select table_name,

CASE

  WHEN owner='SYS' THEN 'The owner is SYS'

  WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'

  ELSE 'The owner is another value'

END

from all_tables;

The above two case statements are equivalent to the following IF-THEN-ELSE statement:

IF owner = 'SYS' THEN

     result := 'The owner is SYS';

ELSIF owner = 'SYSTEM' THEN

    result := 'The owner is SYSTEM'';

ELSE

    result := 'The owner is another value';

END IF;

The case statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted.

Your SQL statement would look as follows:

select table_name,

CASE owner

  WHEN 'SYS' THEN 'The owner is SYS'

  WHEN 'SYSTEM' THEN 'The owner is SYSTEM'

END

from all_tables;

With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

Example:

Here is an example that demonstrates how to use the case statement to compare different conditions:

select

CASE

  WHEN a < b THEN 'hello'

  WHEN d < e THEN 'goodbye'

END

from suppliers;

BETWEEN Condition

The BETWEEN condition allows you to retrieve values within a range.

Syntax:

SELECT columns

FROM tables

WHERE column1 between value1 and value2;

This SQL statement will return the records where column1 is within the range of value1 and value2 (inclusive). The BETWEEN function can be used in any valid SQL statement - select, insert, update, or delete.

Example #1 - Numbers

The following is an SQL statement that uses the BETWEEN function:

SELECT *

FROM suppliers

WHERE supplier_id between 5000 AND 5010;

This would return all rows where the supplier_id is between 5000 and 5010, inclusive. It is equivalent to the following SQL statement:

SELECT *

FROM suppliers

WHERE supplier_id >= 5000

AND supplier_id <= 5010;

Example #2 - Dates

You can also use the BETWEEN function with dates.

SELECT *

FROM orders

WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')

AND to_date ('2003/12/31', 'yyyy/mm/dd');

This SQL statement would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive).

It would be equivalent to the following SQL statement:

SELECT *

FROM orders

WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')

AND order_date <= to_date('2003/12/31','yyyy/mm/dd');

Example #3 - NOT BETWEEN

The BETWEEN function can also be combined with the NOT operator.

SELECT *

FROM suppliers

WHERE supplier_id not between 5000 and 5500;

This would be equivalent to the following SQL:

SELECT *

FROM suppliers

WHERE supplier_id < 5000

OR supplier_id > 5500;

In this example, the result set would exclude all supplier_id values between the range of 5000 and 5500 (inclusive).

AND Operator

The AND condition allows you to create an SQL statement based on 2 or more conditions being met. It can be used in any valid SQL statement - select, insert, update, or delete.

Syntax:

SELECT columns

FROM tables

WHERE column1 = 'value1'

and column2 = 'value2';

The AND condition requires that each condition be must be met for the record to be included in the result set. In this case, column1 has to equal 'value1' and column2 has to equal 'value2'.

Example #1:

 The first example that we'll take a look at involves a very simple example using the AND condition.

SELECT *

FROM suppliers

WHERE city = 'New York'

and type = 'PC Manufacturer';

This would return all suppliers that reside in New York and are PC Manufacturers. Because the * is used in the select, all fields from the supplier table would appear in the result set.

Example #2:

Example #2 demonstrates how the AND condition can be used to "join" multiple tables in an SQL statement.

SELECT    orders.order_id

, suppliers.supplier_name

              FROM suppliers

         , orders

WHERE suppliers.supplier_id = orders.supplier_id

and suppliers.supplier_name = 'ABC';

This would return all rows where the supplier_name is ABC. And the suppliers and orders tables are joined on supplier_id. You will notice that all of the fields are prefixed with the table names (ie: orders.order_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the suppliers and orders tables.

In this case, the result set would only display the order_id and supplier_name fields (as listed in the first part of the select statement.).