• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

How to Use SQL CASE for Conditional Logic in Your SQL Queries

May 15, 2017 by Andy Hayes Leave a Comment

SQL CASE provides the author of the query with the ability to perform conditional logic in their SQL queries for SELECT, INSERT, UPDATE, DELETE. It is also possible to use it with SET, IN, HAVING, ORDER BY and WHERE.

It comes in two formats:

  • simple case
  • search case

Simple SQL CASE

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

What that means in practice is that you can test a value such as “Labrador” and output a value such as “Dog”.

Let’s look at an example. Here is a table script and some data I added.

Table creation script

CREATE TABLE [dbo].[Breeds](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Breed] [varchar](50) NOT NULL,
  [Value] [int] NOT NULL,
 CONSTRAINT [PK_Breeds] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

The data added is here:

SQL CASE sample data

We want to query this data and produce a new column that contains the type of animal as it was not supplied in the original data. Here is the code to do that:

SELECT ID, Breed, Value, 
  CASE
    WHEN Breed = 'Labrador' THEN 'Dog'
    WHEN Breed = 'King Charles Spaniel' THEN 'Dog'
    WHEN Breed = 'Golden Retriever' THEN 'Dog'
    WHEN Breed = 'Yorkshire Terrier' THEN 'Dog'
    WHEN Breed = 'French Bulldog' THEN 'Dog'
    WHEN Breed = 'Siamese' THEN 'Cat'
    WHEN Breed = 'Persian' THEN 'Cat'
    WHEN Breed = 'Bengal' THEN 'Cat'
    WHEN Breed = 'Ragdoll' THEN 'Cat'
    WHEN Breed = 'American Shorthair' THEN 'Cat'
  END AS Animal
FROM Breeds;

Which produces:

SQL CASE example result

Having looked at the data and knowing that it only contains two types of animal, the SQL CASE could also be written in a more simple way using an ELSE:

SELECT ID, Breed, Value, 
  CASE
    WHEN Breed = 'Labrador' THEN 'Dog'
    WHEN Breed = 'King Charles Spaniel' THEN 'Dog'
    WHEN Breed = 'Golden Retriever' THEN 'Dog'
    WHEN Breed = 'Yorkshire Terrier' THEN 'Dog'
    WHEN Breed = 'French Bulldog' THEN 'Dog'
  ELSE 'Cat'
  END AS Animal
FROM Breeds;

Another way to write this would be to use IN:

SELECT ID, Breed, Value, 
  CASE
    WHEN Breed IN ('Labrador', 'King Charles Spaniel','Golden Retriever','Yorkshire Terrier','French Bulldog') THEN 'Dog'
  ELSE 'Cat'
  END AS Animal
FROM Breeds;

Search SQL CASE

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

What that means in practice is that you can make a test such as <= 10 and output a value such as “£0 – 10”.

With the sample data we have been working with, this format can be used with it.

SELECT ID, Breed, Value 
  CASE
    WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
  END AS Animal
FROM Breeds;

The test against the ID column produces a Boolean result and this enables us to produce the same result with less code. This Boolean matching can be really useful when dealing with numeric type data types as it allows rows to be grouped easily based on the numeric value.

For example:

SELECT ID, Breed, Value, 
  CASE
    WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
  END AS Animal,
  CASE 
     WHEN Value < 400 THEN '£0 - £399' 
     WHEN Value < 700 THEN '£400 - £699' 
  ELSE '£700 +' 
  END AS [Price Bracket]
FROM Breeds
ORDER BY Value;

This produces:

SQL CASE example result using Search CASE

Can you spot the bug in the SQL code? What if for some reason another record were added with a value of less than zero. This record would appear in the £0 – £399 price bracket.

So you could handle this in a couple of ways:

  • use between
  • use nested case

Between example:

SELECT ID, Breed, Value, CASE
 WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
 END AS Animal,
 CASE 
   WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' 
   WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' 
   WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket]
FROM Breeds
ORDER BY Value;

Nested case example:

SELECT 
  ID, Breed, Value, CASE WHEN ID <= 5 THEN 'Dog' ELSE 'Cat' END AS Animal,
  CASE WHEN Value > 0 THEN
    CASE 
      WHEN Value < 400 THEN '£0 - £399' 
      WHEN Value < = 700 THEN '£400 - £699' 
      ELSE '£700 +' END 
  END AS [Price Bracket]
FROM Breeds
ORDER BY Value;

Using SQL CASE with ORDER BY

It is possible to use CASE with ORDER BY to order results based on if then else logic. Extending our last query to have an ORDER BY included, the criteria here is to ensure that the ‘Persian’ breed is the first row.

SELECT ID, Breed, 
  CASE
    WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
  END AS Animal
FROM Breeds
ORDER BY CASE WHEN Breed = 'Persian' THEN 1 ELSE 2 END;

This produces:

SQL CASE example result using ORDER BY

Using SQL CASE in an UPDATE statement

If you remember, the data was supplied without an ‘Animal’ column and we’re having to calculate this in the SQL. It would be better to create a new table to contain the animal description and create a relationship between our Breeds table and the new Animals table.

Here is our new table:

CREATE TABLE [dbo].[Animals](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Animal] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Animals] PRIMARY KEY CLUSTERED 
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

First we need to populate it with the animal values. We know we can do this with the help of our earlier use of CASE

WITH cte_Breeds
AS
(
SELECT ID, Breed, Value, CASE
 WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
 END AS Animal,
 CASE 
   WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' 
   WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' 
   WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket]
FROM Breeds
)

INSERT INTO Animals
SELECT DISTINCT Animal FROM cte_Breeds;

This is our data:

SQL CASE related table

We now need to add a new column to the Breeds table to house the ID from the Animals table to complete the relationship.

ALTER TABLE Breeds 
  ADD AnimalID INT NULL,
  FOREIGN KEY(AnimalID) REFERENCES Animals(ID);

Now we will run an UPDATE on our Breeds table using CASE

WITH cte_Breeds
AS
(
SELECT ID, Breed, Value, CASE
 WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
 END AS Animal,
 CASE 
   WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' 
   WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' 
   WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket]
FROM Breeds
)

UPDATE Br
SET AnimalID = CASE WHEN cte.Animal = 'Cat' THEN 1 ELSE 2 END
FROM cte_Breeds cte
JOIN Breeds Br ON cte.ID = Br.ID;

So this has worked successfully as we now have a relationship between the two tables:

SQL CASE with UPDATE

For the purposes of this example, the UPDATE SET CASE is hard coded. This would be a problem if there were many more breeds to update with different animal types and so in that case it would be more efficient not to use CASE. Instead one might do this:

WITH cte_Breeds
AS
(
SELECT ID, Breed, Value, CASE
 WHEN ID <= 5 THEN 'Dog' ELSE 'Cat'
 END AS Animal,
 CASE 
   WHEN Value BETWEEN 0 AND 399 THEN '£0 - £399' 
   WHEN Value BETWEEN 400 AND 699 THEN '£400 - £699' 
   WHEN Value > 699 THEN '£700 + ' END AS [Price Bracket]
FROM Breeds
)


UPDATE Br 
SET AnimalID = An.ID
FROM Animals An 
JOIN cte_Breeds cte ON cte.Animal = An.Animal
JOIN Breeds Br ON cte.ID = Br.ID;

I hope you found this post useful. Check out the official documentation with more examples about SQL CASE here – CASE (Transact – SQL)

Filed Under: All Articles, SQL Tips and Tricks Tagged With: mysql, sql server, t-sql

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Primary Sidebar

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©