SQL Server: CASE with Different Clause
3.91/5 (8 votes)
Using CASE with variable/SET, SELECT/UPDATE statement, WHERE clause, JOIN & ON clause
Background
Here, we are going to check examples of how CASE can be used in any statement or clause that allows a valid expression. For example, we usually use CASE in statements such as SELECT, UPDATE, DELETE and SET. CASE can also be used with JOIN, WHERE, ORDER BY and HAVING clauses. Let us check some examples.
CASE Types
There are two types of CASE:
- Simple CASE: Allows only an equality check
- Searched CASE: Allows multiple checks
Simple CASE
CASE Expression
WHEN Value1 THEN Result1
WHEN Value2 THEN Result2
…
ELSE Result
END
DECLARE @userTypeId INT = 2;
DECLARE @userType VARCHAR(100);
/*Simple CASE*/
SET @userType = CASE @userTypeId
WHEN 1 THEN 'Admin'
WHEN 2 THEN 'User'
ELSE 'Unknown'
END;
SELECT @userType;
Searched CASE
CASE
WHEN BooleanExpression1 THEN Result1
WHEN BooleanExpression2 THEN Result2
…
ELSE Result
END
DECLARE @userTypeId INT = 2;
DECLARE @userType VARCHAR(100);
/*Searched CASE*/
SET @userType = CASE
WHEN @userTypeId = 1 THEN 'Admin'
WHEN @userTypeId = 2 THEN 'User'
ELSE 'Unknown'
END;
SELECT @userType;
Alternatively, we can also do:
DECLARE @inputNumber INT = 2
DECLARE @result NVARCHAR(MAX);
SELECT
@result =
CASE
WHEN ISNUMERIC(@inputNumber) = 1 AND @inputNumber % 2 = 0 THEN 'Even'
WHEN ISNUMERIC(@inputNumber) = 1 AND @inputNumber % 2 = 1 THEN 'Odd'
ELSE 'Not Number'
END;
SELECT @result AS Result;
With Variable
Use Like Switch
DECLARE @userTypeId INT = 2; /*set any number*/
DECLARE @userType VARCHAR(100);
SET @userType = CASE @userTypeId
WHEN 1 THEN 'Admin'
WHEN 2 THEN 'User'
ELSE 'Unknown'
END;
SELECT @userType;
Multiple Compare
DECLARE @inputNumber INT = 2
DECLARE @result NVARCHAR(MAX);
SELECT
@result =
CASE
WHEN ISNUMERIC(@inputNumber) = 1 AND @inputNumber % 2 = 0 THEN 'Even'
WHEN ISNUMERIC(@inputNumber) = 1 AND @inputNumber % 2 = 1 THEN 'Odd'
ELSE 'Not Number'
END;
SELECT @result AS Result;
With SELECT Statement
Use Like Switch
/*use like switch*/
SELECT
Id,
CASE Id
WHEN 1 THEN 'Admin'
WHEN 2 THEN 'User'
ELSE 'Anonymous'
END
FROM (
VALUES (1), (2), (NULL)
) AS Roles(Id)
Multiple Compare
/*multiple compare*/
SELECT
NumberValue,
CASE
WHEN ISNUMERIC(NumberValue) = 1 AND NumberValue % 2 = 0 THEN 'Even'
WHEN ISNUMERIC(NumberValue) = 1 AND NumberValue % 2 = 1 THEN 'Odd'
ELSE 'Not Number'
END
FROM(
VALUES ('1'), ('2'), ('A'), (NULL)
) AS NumberList(NumberValue)
With WHERE Clause
Here are our tables and data:
/*with where*/
DECLARE @tblUserPoints TABLE
(
Name VARCHAR(100),
UserType VARCHAR(100),
Points INT
);
INSERT
INTO @tblUserPoints
VALUES
('Dan', 'Sa', 90000), /*will be selected*/
('Dan1', 'Sa', 80000),
('Ben', 'Admin', 70000), /*will be selected*/
('Ben1', 'Admin', 60000),
('Kim', 'User', 50000), /*will be selected*/
('Kim1', 'User', 40000);
CASE Instead of OR
Here is an example of regular WHERE condition query:
/*regular 'OR' condition*/
SELECT *
FROM @tblUserPoints
WHERE (UserType = 'Sa' AND Points > 80000)
OR (UserType = 'Admin' AND Points > 60000)
OR (UserType = 'User' AND Points > 40000)
Rewriting above query using CASE:
/*using 'CASE' instated of 'OR'*/
SELECT *
FROM @tblUserPoints
WHERE (
CASE
WHEN UserType = 'Sa' AND Points > 80000 THEN 1
WHEN UserType = 'Admin' AND Points > 60000 THEN 1
WHEN UserType = 'User' AND Points > 40000 THEN 1
ELSE NULL
END
) IS NOT NULL
The same way we can do things with AND conditions.
Switch Between Conditions Depending On Flag
Here, depending on @flag, the value we are actually switching between WHERE condition:
/*switch between conditions depending on a flag*/
DECLARE @flag VARCHAR(50);
--SET @flag = 'Best';
--SET @flag = 'Better';
SET @flag = 'Good';
SELECT *
FROM @tblUserPoints
WHERE (
CASE
WHEN @flag = 'Best' AND Points >= 80000 THEN 1
WHEN @flag = 'Better' AND Points >= 60000 AND Points < 80000 THEN 1
WHEN @flag = 'Good' AND Points >= 40000 AND Points < 60000 THEN 1
ELSE NULL
END
) IS NOT NULL
With JOIN and ON Clause
Our tables and data:
/*data*/
DECLARE @tblReputationTotal TABLE
(
TotalPoint INT NULL,
Reputation VARCHAR(100)
);
DECLARE @tblReputation TABLE
(
Point INT NULL, /*sa*/
RankPosition INT NULL, /*admin*/
Rating INT NULL, /*user*/
Reputation VARCHAR(100)
);
DECLARE @tblUserPoint TABLE
(
Name VARCHAR(100),
UserType VARCHAR(100),
Point INT NULL,
RankPosition INT NULL,
Rating INT NULL
);
INSERT
INTO @tblReputationTotal (TotalPoint, Reputation)
VALUES
(90000, 'Best'),
(70000, 'Better'),
(50000, 'Good');
INSERT
INTO @tblReputation (Point, RankPosition, Rating, Reputation)
VALUES
(90000, NULL, NULL, 'Good Sa'),
(NULL, 70000, NULL, 'Good Admin'),
(NULL, NULL, 50000, 'Good User');
INSERT
INTO @tblUserPoint (Name, UserType, Point, RankPosition, Rating)
VALUES
('Dan', 'Sa', 90000, NULL, NULL), /*will be selected, Point*/
('Dan1', 'Sa', 80000, NULL, NULL),
('Ben', 'Admin', NULL, 70000, NULL), /*will be selected, RankPosition*/
('Ben1', 'Admin', NULL, 60000, NULL),
('Kim', 'User', NULL, NULL, 50000), /*will be selected, Rating*/
('Kim1', 'User', NULL, NULL, 40000);
Here, depending on UserType column value, we are selecting join column:
/*query*/
SELECT u.*, r.Reputation
FROM @tblUserPoint AS u
JOIN @tblReputation AS r
ON CASE
WHEN u.UserType = 'Sa' AND u.Point = r.Point THEN 1
WHEN u.UserType = 'Admin' AND u.RankPosition = r.RankPosition THEN 1
WHEN u.UserType = 'User' AND u.Rating = r.Rating THEN 1
ELSE 0
END = 1;
Alternatively, we can also do:
/*query*/
SELECT u.*, r.Reputation
FROM @tblReputationTotal AS r
JOIN @tblUserPoint AS u
ON r.TotalPoint =
CASE
WHEN u.UserType = 'Sa' THEN u.Point
WHEN u.UserType = 'Admin' THEN u.RankPosition
WHEN u.UserType = 'User' THEN u.Rating
ELSE 0 /*can remove ELSE or set to -1. Don't set it to NULL*/
END
With Update
DECLARE @tblBalance TABLE (Amount INT NULL, NumberType VARCHAR(100) NULL);
INSERT INTO @tblBalance(Amount) VALUES (1), (2), (3), (4), (NULL);
/*update*/
UPDATE @tblBalance
SET NumberType = CASE
WHEN Amount % 2 = 1 THEN 'Odd'
WHEN Amount % 2 = 0 THEN 'Even'
ELSE 'Null Value'
END;
SELECT * FROM @tblBalance;
With ORDER BY
Our tables and data:
/*data*/
DECLARE @tblEmployee TABLE
(
Id INT,
UserName VARCHAR(100),
Email VARCHAR(100)
);
INSERT
INTO @tblEmployee (Id, UserName, Email)
VALUES
(1, 'Dan', 'Dan@gmail.com'),
(2, 'Hen', 'Han@gmail.com'),
(3, 'Ben', 'Ben@gmail.com');
DECLARE @orderFilter VARCHAR(100);
SET @orderFilter = 'LoginName';
--SET @orderFilter = 'Email';
To manage order, we are using an order indicator flag @orderFilter.
@orderFilter will hold the expected column name or logic to be used for ordering a particular data set.
Different Columns But Same Order
Here, we are switching between columns UserName or Email or None('') but using the same order DESC:
SELECT *
FROM @tblEmployee
ORDER BY
(
CASE @orderFilter
WHEN 'LoginName' THEN UserName
WHEN 'Email' THEN Email
ELSE '' /*need varchar, type as username
and email are varchar*/
END
) DESC, /*can do individual column wise,
check next query*/
Id ASC
Column Wise Order
It is a bit different from the previous example. Here, order type can be specific to each column.
SELECT *
FROM @tblEmployee
ORDER BY
CASE WHEN @orderFilter = 'LoginName' THEN UserName END DESC,
CASE WHEN @orderFilter = 'Email' THEN Email END ASC,
Id ASC
UserName will be ordered as DESC or Email will be ordered as ASC depending on the column selection flag @orderFilter value.
Others
With GROUP BYhttps://www.essentialsql.com/sql-case-statement/With HAVING
Limitations
ORis not supported inside the boolean expression, instead ofORuseIN()- Avoid nested
CASE, SQL Server allows for only 10 levels of nesting inCASEexpressions. Instead use moreWHEN
References
- Core: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017
- Join: https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition
- Order: https://stackoverflow.com/questions/19486882/case-when-statement-for-order-by-clause
History
- 5th July, 2022: Initial version