The CASE expression in SQL Server is used to create conditional logic within SQL queries. It allows you to perform different actions based on different conditions, similar to IF-THEN-ELSE statements in other programming languages.
Introduction to CASE Expression
The CASE expression can be used in several parts of a SQL query, including the SELECT list, WHERE clause, ORDER BY clause, and others. It provides a way to return specific values based on certain conditions.
Syntax of CASE Expression
There are two types of CASE expressions: the simple CASE expression and the searched CASE expression.
Simple CASE Expression
The simple CASE expression compares an expression to a set of simple expressions to determine the result.
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE resultN
END
# Searched CASE Expression
The searched CASE expression evaluates a set of Boolean expressions to determine the result.
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
ELSE resultN
END
Examples of CASE Expressions
Example 1: Simple CASE Expression
Consider a table Employees with the following data:
We want to display a specific message based on the JobTitle.
SELECT EmployeeID, Name, JobTitle,
CASE JobTitle
WHEN ‘Developer’ THEN ‘Works on coding’
WHEN ‘Analyst’ THEN ‘Works on analysis’
WHEN ‘Manager’ THEN ‘Manages the team’
ELSE ‘Other role’
END AS JobDescription
FROM Employees;
Example 2: Searched CASE Expression
Using the same Employees table, we want to categorize salaries based on ranges.
Assume we have a table Salaries:
We want to categorize salaries into low, medium, and high.
SELECT EmployeeID, Salary,
CASE
WHEN Salary < 50000 THEN ‘Low’ WHEN Salary BETWEEN 50000 AND 70000 THEN ‘Medium’ WHEN Salary > 70000 THEN ‘High’
END AS SalaryCategory
FROM Salaries;Using CASE in WHERE Clause
The CASE expression can also be used in the WHERE clause to apply conditional logic.
SELECT EmployeeID, Name, JobTitle
FROM Employees
WHERE
CASE
WHEN JobTitle = ‘Developer’ THEN 1
ELSE 0
END = 1;# Using CASE in ORDER BY Clause
You can use CASE to conditionally order results.
SELECT EmployeeID, Name, JobTitle, Salary
FROM Employees
ORDER BY
CASE
WHEN JobTitle = ‘Manager’ THEN 1
WHEN JobTitle = ‘Developer’ THEN 2
ELSE 3
END;
Handling NULL Values with CASE
The CASE expression can handle NULL values effectively.
Consider a table Products with possible NULL values in the Discount column:
We want to replace NULL with ‘No Discount’.
SELECT ProductID, ProductName,
CASE
WHEN Discount IS NULL THEN ‘No Discount’
ELSE CAST(Discount AS VARCHAR) + ‘%’
END AS DiscountDescription
FROM Products;Nested CASE Expressions
You can nest CASE expressions to handle more complex logic.SELECT EmployeeID, Name, Salary,
CASE
WHEN Salary < 50000 THEN ‘Low’ WHEN Salary BETWEEN 50000 AND 70000 THEN CASE WHEN JobTitle = ‘Developer’ THEN ‘Medium – Developer’ ELSE ‘Medium’ END WHEN Salary > 70000 THEN ‘High’
END AS SalaryCategory
FROM Employees
JOIN Salaries ON Employees.EmployeeID = Salaries.EmployeeID;
The CASE expression is a powerful tool in SQL that allows you to implement conditional logic directly in your queries. It enhances the flexibility and readability of your SQL code. Practice using CASE expressions in different parts of your queries to become proficient in applying conditional logic in SQL.