CASE and conditional expressions

Express business rules inside a query.

UnderstandStudy an examplePractise

Understand the concept

CASE is an expression that returns a value; it does not control an entire query like procedural IF. A simple CASE compares equality, while a searched CASE evaluates conditions in order. The first matching WHEN wins. Without ELSE, unmatched rows yield NULL.

Branch values must resolve to a compatible type. Mixing numbers and text can trigger conversion errors. Do not assume CASE prevents every divide-by-zero situation, especially when aggregates may be evaluated earlier. Use NULLIF to guard the denominator explicitly.

WHEN TO USE IT

Use it for labels, segments, conditional totals and ordering rules.

COMMON PITFALL

Treating CASE as flow control or overlooking a missing ELSE.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT ProductName, CASE WHEN Stock = 0 THEN N'Unavailable' ELSE N'In stock' END AS Availability FROM lab.Product;

Cable is unavailable; the other products are in stock.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Label prices at least 5000 as High and the rest as Regular.

Show the solution and explanation

Worked solution

SELECT ProductName, CASE WHEN Price >= 5000 THEN N'High' ELSE N'Regular' END AS PriceBand FROM lab.Product;

The label exists only in the result; it changes no stored price.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.