Lesson 6 - Conditional Logic with CASE and Clean Style

Welcome to Conditional Logic with CASE#

WHERE removes rows that do not match a condition. But what if you want to keep every row and instead label each one based on a condition? That is exactly what the CASE expression does — it is SQL’s version of “if this, then that.” With CASE you can flag records, bin numbers into groups, and regroup categories, all inside a single query.

This lesson also closes the module with a short look at writing performant SQL — queries that run quickly even against millions of rows.

By the end of this lesson, you will be able to:

• Write CASE expressions to flag rows with two outcomes
• Bin continuous numbers into named groups
• Regroup categorical values, and understand what happens without ELSE
• Sort results by a CASE expression
• Apply best practices for writing performant SQL

You should be comfortable with SELECT, WHERE, ORDER BY, and aliases. Let’s begin.

Conditional Logic with CASE#

A CASE expression evaluates conditions in order and returns a value for the first one that is true. Its building blocks are:

• WHEN ... THEN ... — a condition and the value to return if it is met
• ELSE ... — a catch-all value for everything not matched above
• END — signals the end of the expression

Flagging Rows with Two Outcomes#

The simplest CASE has one WHEN and an ELSE. To flag whether each order’s sales exceed $75:

SELECT order_id, sales,
       CASE WHEN sales > 75 THEN 'Yes'
            ELSE 'No'
       END AS sales_over_75
  FROM orders
 LIMIT 5;

Every row is kept; each gets a Yes or No in the new sales_over_75 column. Without the alias, the column would be named after the whole CASE expression, so always alias your CASE columns. The condition inside WHEN can be anything a WHERE clause accepts — for instance, WHEN region IN ('Central', 'South') THEN 'Territory 1' ELSE 'Territory 2' splits regions into two territories.

Binning Numbers into Groups#

With two outcomes covered, you can chain several WHEN ... THEN ... statements to sort values into multiple groups. This is called binning.

Multiple WHEN Statements#

To classify each sale as small, medium, or large:

SELECT order_id, product_id, sales,
       CASE
           WHEN sales BETWEEN 0 AND 49.99  THEN 'small sale'
           WHEN sales BETWEEN 50 AND 99.99 THEN 'medium sale'
           ELSE 'large sale'
       END AS sales_size
  FROM orders
 LIMIT 5;

SQL checks each WHEN from top to bottom and returns the first match; anything left over falls to ELSE. You can add as many WHEN clauses as you need. This turns raw numbers into human-readable categories — exactly the kind of thing a dashboard needs.

Regrouping Categories and the Missing ELSE#

CASE works on text too, which is perfect for collapsing several categories into broader groups.

Bundling Values into “Other”#

The region field has four values: South, West, Central, and East. Suppose you want to keep West and East as-is but bundle the rest into “Other”:

SELECT DISTINCT city, state, region,
       CASE
           WHEN region = 'West' THEN 'West'
           WHEN region = 'East' THEN 'East'
           ELSE 'Other'
       END AS region_regroup
  FROM orders;

South and Central both become “Other,” while West and East keep their names.

What Happens Without ELSE#

The ELSE is optional — but watch what happens when you drop it:

SELECT DISTINCT city, state, region,
       CASE
           WHEN region = 'West' THEN 'West'
           WHEN region = 'East' THEN 'East'
       END AS region_regroup
  FROM orders;

Rows that match no WHEN get a NULL (a blank). This is the rule to remember: a CASE with no matching WHEN and no ELSE returns NULL. Sometimes that is exactly what you want — for example, labeling only “Great” and “Terrible” profit margins and leaving the middle blank — but if you expect every row to get a label, always include an ELSE.

Ordering by a CASE Expression#

So far you have sorted by columns. But ORDER BY accepts any expression, including CASE. This lets you impose a custom order that is neither alphabetical nor numeric.

Custom Sort Order#

Suppose you want to display orders with “Same Day” shipping first, then “First Class,” then everything else. You cannot use ORDER BY ship_mode because alphabetically “First Class” comes before “Same Day.” Instead, assign each value a numeric rank with CASE:

SELECT order_date, order_id, ship_mode,
       CASE
           WHEN ship_mode = 'Same Day'    THEN 1
           WHEN ship_mode = 'First Class' THEN 2
           ELSE 3
       END AS ship_priority
  FROM orders
 ORDER BY ship_priority;

“Same Day” floats to the top because it was ranked 1. If you do not want the rank column cluttering your output, move the entire CASE expression into ORDER BY:

SELECT order_date, order_id, ship_mode
  FROM orders
 ORDER BY CASE
              WHEN ship_mode = 'Same Day'    THEN 1
              WHEN ship_mode = 'First Class' THEN 2
              ELSE 3
          END;

The sort order is identical, but the rank no longer appears as a column. Note that a CASE in ORDER BY does not need an alias. This technique is invaluable whenever you need a meaningful, business-defined order — say, showing the most profitable customer segment first.

Writing Performant SQL#

To close the module, a word on performance. Databases can hold millions of rows, and a sloppy query can be painfully slow. A query is called performant when it runs efficiently. A few habits — all using clauses you already know — go a long way:

1. Avoid SELECT *. Select only the columns you actually need. Pulling every column wastes time and memory.
2. Sample with LIMIT. While exploring, cap your output so you are not rendering millions of rows.
3. Prefer IN over chained OR. WHERE region IN ('East', 'West') is evaluated more efficiently than WHERE region = 'East' OR region = 'West'.

Putting it together, the most performant way to preview sales and profit for two regions is:

SELECT sales, profit
  FROM orders
 WHERE region IN ('East', 'West')
 LIMIT 10;

It selects only the needed columns, uses IN instead of OR, and limits the rows. None of this is about clever tricks — it is about being deliberate, which keeps your queries fast and your databases happy.

Practice Exercises#

Use the orders table.

Exercise 1: Territory Labels#

Write a query returning order_id, state, sales, and a territory column. Use CASE to label the Central and South regions as ‘Territory 1’ and all other regions as ‘Territory 2’.

Exercise 2: Shipping Priority Labels#

Write a query returning order_date, order_id, ship_mode, and a ship_priority column. Label ‘Same Day’ as ‘High Priority’, ‘First Class’ as ‘Medium Priority’, and everything else as ‘Low Priority’. Sort so the most recent orders appear first.

Exercise 3: Custom Segment Order#

For the city of Watertown, write a query returning segment, subcategory, product_name, sales, and profit, ordered so the Corporate segment appears first, then Consumer, then any others.

Summary#

You added conditional logic to your SQL toolkit. You flagged rows, binned numbers, regrouped categories, learned how a missing ELSE produces NULL, sorted by a CASE expression, and picked up the habits of performant SQL.

Key Concepts#

• CASE — returns a value based on the first matching condition; built from WHEN ... THEN ..., an optional ELSE, and END.
• Flagging — a single WHEN/ELSE produces a two-value label.
• Binning — chaining multiple WHEN clauses sorts numbers into named groups.
• Regrouping — CASE collapses several categories into broader ones; without a matching WHEN and no ELSE, the result is NULL.
• ORDER BY CASE — sorts by a computed expression for custom, non-alphabetical order; needs no alias.
• Performant SQL — avoid SELECT *, sample with LIMIT, and prefer IN over chained OR.

Why This Matters#

Raw values rarely tell a story on their own — stakeholders want categories, tiers, and priorities they can act on. CASE is how you translate continuous, messy data into the clean buckets that drive decisions and dashboards. Sorting by a CASE expression lets you present data in the order the business actually thinks in, not just alphabetical order. And writing performant queries is what makes you trustworthy with real, large databases, where a careless SELECT * can bring a report to a crawl. Together, these skills mark the transition from writing queries that work to writing queries that are genuinely useful.

Next Steps#

Continue Building Your Skills#

CASE is one of the most expressive tools in SQL — it turns plain data into the categories and orderings people actually care about. Combine it with the filtering and sorting you already know, and you can answer remarkably nuanced questions. Next, you will bring everything together in a hands-on guided project.