How to Use a CASE Statement in SQL

By James Highland

The Structured Query Language (SQL) is a programming platform for databases. It is possible to create a database from scratch, enter data, extract data, sort, filter and analyze data, and create new data arguments off existing data entirely with this language. The "CASE" statement is an example of the power of SQL. This command allows the database to return information about its data without that information actually being stored directly within the database. The single statement extracts data, analyzes it and then provides a specific type of output based on a conditional decision-making algorithm.

Things You'll Need

  • SQL database access

Step 1

Create a new SQL "SELECT" query. All interactions with a database are handled through queries. The "SELECT" query is the most-used function in SQL, as this command pulls data from the database. The "CASE" statement is integrated with "SELECT."

Step 2

Create the beginning of the "SELECT" statement that selects which database fields should be extracted for display in the query's output. For example, type "SELECT product" if you wish the query to show the name of the product in its output.

Step 3

Type a comma after the chosen field(s) you want to display in the query output.

Step 4

Type the name of the new variable that will hold the output of the "CASE" statement. This variable name should not already exist in the database. Place this variable name in single quotes and then type an equal sign after the name.

Step 5

Type "CASE". Note that all SQL commands, including "CASE," must be entered in all capital letters. This part of the "SELECT" query begins the "CASE" statement that will assign a value to the new variable.

Step 6

Create as many "WHEN/THEN" statements as you need to isolate all the conditions that should affect output. Each statement is typed in a row. For example, if you want to test the value of another field for the extracted record, create a statement such as "WHEN quantity > 0 THEN 'in stock." Note the double quotes are not included in the actual program code. This statement will assign the value "in stock" to the variable previously created.

Step 7

Type "END" after you have created the "WHEN" statements. This ends the "CASE" statement section of the query.

Step 8

Type the rest of the "SELECT" query as normal. The entire "CASE" statement will create a unique output for each record that is extracted by the query.

Tips & Warnings

  • You may optionally include an "ELSE" parameter in your "WHEN" statement.

References & Resources