Master Case SQL Server Tutorial W3Schools Guide

Case statements in SQL Server are powerful tools for controlling program flow and performing specific actions based on certain conditions. They allow for more dynamic and versatile code by enabling you to direct SQL Server's behavior in response to different scenarios. Let's explore how to use CASE statements, their syntax, types, and some practical examples.

SQL CASE WHEN Explained with Examples | Beginner SQL Tutorial

Before delving into the specifics, let's set the stage with a simple yet common scenario. Imagine you're writing a query to fetch employee data, and you want to display their title as 'Manager' if their department is 'Sales', and 'Admin' for any other department. This is where a CASE statement comes in, helping to govern how SQL Server handles and presents the data.

an image of the server list for windows and mac oss servers, with different types of

SQL Server CASE Statement Syntax and Types

The basic syntax of a CASE statement in SQL Server is as follows:

Learn SQL Fast 🚀 | Beginner SQL Commands Cheat Sheet for Data Analysts

CASE condition
     WHEN value1 THEN result1
     WHEN value2 THEN result2
     ...
     ELSE result
END

The CASE statement evaluates a specified condition and, depending on the result, returns one value (result1, result2, etc.) or another in case no WHEN condition matches (ELSE result).

Simple CASE

In the simple CASE, you specify just one condition, which SQL Server evaluates and compares with the provided WHEN values.

Example: Let's revise the previous employee title scenario using a simple CASE statement:

SELECT Name, Department,
CASE Department
    WHEN 'Sales' THEN 'Manager'
    ELSE 'Admin'
END AS Title
FROM Employees

Searched CASE

SQL Basics Cheat Sheet for Beginners | Learn SQL Fast 💻

The searched CASE, unlike the simple CASE, evaluates each WHEN condition and returns the result for the first true condition. If no conditions are true, it returns the result specified in the ELSE clause.

Example: Let's calculate a discount based on customer loyalty points (a searched CASE statement would be more suitable here):

SELECT Name, Points,
CASE
    WHEN Points >= 1000 THEN '10%'
    WHEN Points >= 500 THEN '5%'
    ELSE 'No discount'
END AS Discount
FROM Customers

Using CASE in the SELECT Clause and ELSE

Sql Cheat Sheet, Learn Sql, Learn Web Development, Excel Tutorials, Sql Server, Career Options, Data Analyst, Data Scientist, Data Analysis

CASE statements are often used in the SELECT clause to return a formatted value or perform computations. The ELSE clause is crucial as it serves as a catch-all, providing a default result when no WHEN conditions are met.

Case in SELECT Clause

SQL UPPER() Explained with Examples | Convert Text to Uppercase in SQL

SQL Joins Explained with Examples | Complete JOIN Cheat Sheet for Beginners

SQL Essentials Cheat Sheet | The Only SQL Notes You'll Need for Revision

7 SQL Projects for Beginners | Build a Real Data Portfolio

SQL CTE Explained with Examples | Common Table Expression Tutorial

Complete SQL Cheat Sheet for Beginners | SQL Commands, Joins, Queries & Database Guide

a blue and white poster with information about different types of dental equipment on it's side

SQL Cheat Sheet for Beginners & Interviews

SQL COALESCE() Explained with Examples | Handle NULL Values in SQL

SQL Joins Cheat Sheet Every Developer Should Save

SQL Query Cheat Sheet | Beginner SQL Commands, Syntax & Examples

Example: Fetch employee name and a label indicating their status (active or inactive) based on the last_login field:

SELECT Name,
CASE WHEN DATEDIFF(day, last_login, GETDATE()) > 90 THEN 'Inactive'
ELSE 'Active'
END AS Status
FROM Employees

Using ELSE in CASE Statements

Including an ELSE clause in your CASE statement guarantees that you'll always get a result, even when none of the conditions are met.

Example: Assign a salary grade based on the employee's salary. If the salary is below 30000, we assign 'Low', and if none of the other conditions are met, we'll call it 'High':

SELECT Name, Salary,
CASE Salary
    WHEN Salary BETWEEN 30000 AND 50000 THEN 'Medium'
    WHEN Salary BETWEEN 50000 AND 100000 THEN 'High'
    ELSE 'Low'
END AS SalaryGrade
FROM Employees

Now that we've explored different types of CASE statements and various use cases, it's clear that they play a crucial role in enhancing the functionality and readability of your SQL Server code. So go forth and make your SQL Server queries even more powerful with the versatile CASE statement! Thinking ahead, why not try using nested CASE statements to create even more complex and dynamic queries?