SQL for .NET developers Beginner

CASE expressions

CASE adds if-then-else logic inside a query. This lesson shows simple and searched CASE, using it to label rows, to sort in a custom order and to count several things in one pass with conditional aggregation, a pattern that replaces many separate queries on big tables.

How CASE picks a value
  1. StartRow arrivesCASE is evaluated once per row
  2. Step 2First WHENIf true, return its THEN value
  3. Step 3Next WHENChecked only if earlier ones were false
  4. EndELSEUsed when nothing matched; NULL if missing

Two forms of CASE

A simple CASE compares one value with a list; a searched CASE checks a condition in each WHEN. The first matching WHEN wins, and ELSE covers everything else (without ELSE you get NULL).

SQL
SELECT OrderId,
       CASE Status                       -- simple CASE
           WHEN 'New'     THEN N'Waiting for payment'
           WHEN 'Paid'    THEN N'Being packed'
           WHEN 'Shipped' THEN N'On the way'
           ELSE N'Closed'
       END AS StatusText,
       CASE                              -- searched CASE
           WHEN Total >= 10000 THEN 'Large'
           WHEN Total >= 1000  THEN 'Medium'
           ELSE 'Small'
       END AS Size
FROM Orders;

Custom sort order

CASE in ORDER BY lets you sort by business meaning instead of alphabetically.

SQL
SELECT OrderId, Status
FROM Orders
ORDER BY CASE Status WHEN 'New' THEN 1 WHEN 'Paid' THEN 2
                     WHEN 'Shipped' THEN 3 ELSE 4 END,
         OrderedAt DESC;

Conditional aggregation

Put CASE inside SUM or COUNT to compute several totals in one scan of the table. On a large table this is far faster than running one query per status.

SQL
SELECT YEAR(OrderedAt) AS [Year],
       COUNT(*)                                           AS Orders,
       SUM(CASE WHEN Status = 'Cancelled' THEN 1 ELSE 0 END) AS Cancelled,
       SUM(CASE WHEN Status <> 'Cancelled' THEN Total END)   AS Revenue
FROM Orders
GROUP BY YEAR(OrderedAt);

Good to know

  • IIF(condition, a, b) is a short CASE with one condition.
  • All branches should return the same type; mixing text and numbers causes conversion errors.
  • CASE is an expression, not a statement: it returns a value and can go anywhere a value can (SELECT, WHERE, ORDER BY, GROUP BY, UPDATE ... SET).
  • In C#, the same idea is a switch expression; in LINQ to EF Core, the conditional operator ?: is translated to CASE.

Interview question

What is conditional aggregation and why is it useful?

It means putting a CASE expression inside an aggregate, for example SUM(CASE WHEN Status = 'Cancelled' THEN 1 ELSE 0 END). One GROUP BY query then returns several counts or totals side by side, reading the table once instead of once per condition, which is much faster on large tables and simpler for reports.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.