SQL Select Case Tutorial

SQL Select Case Tutorial

SQL is a powerful data manipulation language. It provides a mechanism for accessing and modifying data stored in relational tables. One feature supporting the overall power of SQL is the support for case statements. A case statement is functionally equivalent to creating a series of "if" statements for processing code. A simple case statement compares one variable or field against a set of results. A searched case expression compares a set of Boolean expressions to determine the result. In either case, different actions can be performed based on the result of the case statement.

Instructions

Using Searched CASE Statements

    1

    Type the beginning of the CASE statement:

    Select CASE WHEN [your case]

    Replace [your case] with the comparison you want performed. For instance, to compare gender values of M or F, you would type:

    Select CASE WHEN Gender='F'

    2

    Type the action statement. This is the THEN clause, which tells the SQL server what to do when the condition from the WHEN matches. Type the following:

    THEN [perform action]

    Replace [perform action] with your desired result. Continuing the example from Step 2, the gender comparison would add:

    THEN 'Female'

    3

    Type the default action to perform followed by "End Case" if using DB2 or MySQL, or "End" if using Microsoft SQL, Oracle or Sybase databases.

    ELSE [another action] END CASE

    Replace [another action] with the desired result to be produced when the case statement condition(s) are false. For our gender example, we would add:

    ELSE 'Male'

    4

    Execute the statement to examine the results.

Using Simple CASE Statements

    5

    Determine the expression for values to be tested against.

    6

    Type:

    Select CASE [expression]

    Replace [expression] with the field name or query result to be tested.

    7

    Create the WHEN/THEN statement.

    WHEN 'F' THEN 'Female'

    The difference between the searched case and the simple case is in this step. Here, it is not necessary to repeat the value to be tested. That value does not change on each comparison and is listed immediately after the CASE keyword.

    8

    Type the ELSE statement if desired, followed by either "END" or "END CASE". Microsoft SQL, Oracle, or Sybase use END. MySQL and DB2 use END CASE.

    ELSE 'Male' END CASE

    9

    Execute the query and examine the results.

Blog Archive