SQL: Case Statement

The Case Statement is SQL’s version of IF/THEN logic statements.

In this example, I am using the AdventureWorks2012 database, which is free to download. You do not need this to follow the lesson, but if you want to play along, follow this link: SQL: SELECT Statement

 

Let’s start by looking at our data, using the following Select Statement:

SELECT 
 [NationalIDNumber]
 ,[LoginID]
 ,[JobTitle]
 ,[BirthDate]
 ,[MaritalStatus]
 ,[Gender]
 ,[HireDate] 
 FROM [AdventureWorks2012].[HumanResources].[Employee]

Here is the output

Now what if you don’t really care about the particular employee birthday, you simply want to know who is over 40 and who is 40 or younger.

To do that, we need a mechanism to determine an age above or below 40 from the birth date. In most programming languages, you would simply use an If / Then statement. In SQL, we need to use a Case Statement.

The Case Syntax is simple. Let’s assume a variable A, set to either 1 or 0 representing True or False.

Case
     when A = 1 Then "True"
     when B = 0 Then "False"
End

Note the statement is started with the keyword Case and closed with the keyword End.

In the middle, the logic is controlled using When and Then. When <condition> Then  <Action>

If needed, you can append an Else to your statement to cover conditions not specified in your statements.

Case
     when A = 1 Then "True"
     when B = 0 Then "False"
     else "Neither" 
End

 

 

Now, let’s apply it to our case above. Notice the Case statement is put right into my Select statement just as if it were a common field.

select loginID,
 [NationalIDNumber]
 ,[LoginID]
 ,[JobTitle]
 ,
 case 
    when BirthDate < '1977-01-01' then 'Over40'
    when BirthDate >= '1977-01-01' then 'Under40'
 end
 ,[MaritalStatus]
 ,[Gender]
 ,[HireDate]
 FROM [AdventureWorks2012].[HumanResources].[Employee]

Here are my results. You will note a new column with values Over40 or Under40

But what does that column mean? Let’s name it to make it clear.  After the End keyword add as Over40

 select loginID,
 [NationalIDNumber]
 ,[LoginID]
 ,[JobTitle],
 case 
        when BirthDate < '1977-01-01' then 'Over40'
         when BirthDate >= '1977-01-01' then 'Under40'
 end as Over40,
 [MaritalStatus]
 ,[Gender]
 ,[HireDate]
 FROM [AdventureWorks2012].[HumanResources].[Employee]

Here, now the column has a name.