SQL: CASE Statement

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

The SQL CASE statement acts like a switchboard: based on a value, a specific line of processing will be followed. This lesson covers the syntax of the statement as well as some examples.

The CASE statement

In SQL, the CASE statement is like a menu. Based on the value of a given field, certain instructions are carried out. The CASE logic is common in many programming languages, and is much more intuitive (and easier to follow) than a huge if/then/else block of code.

Instead of if/then/else, the CASE statement makes use of WHEN and THEN to determine the branching, while the ELSE clause is a default/catch-all phrase. At the end of the statement, the keyword END is required.

Further, the CASE statement should always go inside the SELECT statement. After the END statement, continue the bulk of the SQL.

This may sound confusing, so let's look at the syntax by using the example of selecting a genre from an artist table. If the genre ID is 1, 'Rock' will be displayed, if it's 2, 'Classical' will be displayed, and otherwise, a generic message will be displayed.


SELECT artistID
  CASE genreID
{   WHEN 1 THEN 'Rock'
   WHEN 2 THEN 'Classical'
   ELSE 'Some Other Genre'
  END
FROM tblArtist;


The table below breaks down the different arguments needed.

Parameter/Argument Notes
Expression The statement after the SELECT statement
Values The value of the field. In the syntax example, the value of genreID. These are preceded by the WHEN clause
Conditions These tell SQL what to do. After the THEN clause, we state what should happen. In this case we will display a value based on the genre ID.
ELSE What if none of the conditions are met? The else statement covers that event. If you leave this statement out, the statement will return a NULL. This isn't bad, but it's best to include the ELSE statement.
END Tells the database you are done with the CASE statement, and to continue processing.

Examples

Let's take a look at some more examples and options that the CASE statement offers.

More Than One Condition

You aren't limited to a single value in the WHEN statements. In the example below, we can check for a regionID of greater than 1000 or equal to 2.


SELECT employeeID
  CASE regionID
   WHEN regionID > 1000 THEN 'Northeast'
   WHEN 2 THEN 'Southwest'
   ELSE 'Some Other Region'
  END
FROM tblEmployee;


Conditions are evaluated in order from top to bottom (as written), so it is possible to have overlap. Can you see where overlap could occur in the following statement?


SELECT employeeID
  CASE regionID
   WHEN regionID > 500 THEN 'Northeast'
   WHEN regionID > 300 THEN 'Southheast'
   WHEN regionID > 200 THEN 'Midwest'
   WHEN regionID > 100 THEN 'Northwest'
   ELSE 'Some Other Region'
  END
FROM tblEmployee;


Overlap may be OK but it can hog system resources, so should be avoided. In this example, any value over 500 AND over 300 will overlap for any value greater than 300. It's redundant. A better way to write this, avoiding confusion, and extra processing would be:


SELECT employeeID
  CASE regionID
   WHEN regionID > 500 THEN 'Northeast'
   WHEN regionID > 300 AND regionID <= 500 THEN 'Southheast'
   WHEN regionID > 200 AND regionID <= 300 THEN 'Midwest'
   WHEN regionID > 100 AND regionID <= 200 THEN 'Northwest'
   ELSE 'Some Other Region'
  END
FROM tblEmployee;


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support