← All Kits · SQL Kit

CASE WHEN With Overlapping Conditions in SQL

Michael Nocito · Updated August 2026 · Every query on this page was run before it was published

This page gives you a two line test that tells you whether a CASE expression is quietly deciding something on your behalf. It matters because overlapping conditions do not error and do not double count. They pick one answer, and the answer is whichever line you happened to write first. On the ten customers below, moving a single line changed the VIP count from 2 to 4.

What you do: write one flag column per rule, then count the rows where two flags are 1. Those are the rows your labelled version had to choose for. If that number is not zero, the order of your WHEN lines is a business decision, and somebody should have made it on purpose.

The short version. CASE stops at the first true condition. So when two conditions can both be true, the order is the logic.

The table the examples run on

Ten customers, three columns that matter: what they have spent, when they signed up, and when they last ordered. Today is 23 August 2026 for every calculation on this page.

CREATE TABLE customers(name TEXT, spend INT, signed_on TEXT, last_order TEXT);

INSERT INTO customers VALUES
('Alvarez',1400,'2026-08-14','2026-08-20'),
('Brennan',1250,'2025-02-03','2026-08-18'),
('Cho',      90,'2026-08-09','2026-08-09'),
('Diaz',    620,'2025-11-20','2026-03-02'),
('Ellis',  2100,'2024-06-01','2026-02-11'),
('Fowler',  310,'2026-08-01','2026-08-16'),
('Grant',   180,'2025-09-14','2026-04-30'),
('Hale',   1050,'2026-08-20','2026-08-21'),
('Iyer',    460,'2025-05-05','2026-08-12'),
('Jansen',  NULL,'2025-07-07','2026-01-15');

Three segment rules, the kind any marketing team asks for. New means signed up in the last 30 days. VIP means spend of 1000 or more. At risk means no order in over 90 days. Every one of them is reasonable on its own.

Two rules, one customer, and no error

Before the query: Hale signed up on 20 August and has spent 1050. Which of the three rules does Hale meet?

Two of them. Hale is new and Hale is a VIP. Nothing in SQL objects to that, because CASE is not checking your rules against each other. It walks the WHEN lines from the top, takes the first one that is true, and stops.

SELECT name, spend,
       CASE WHEN julianday('2026-08-23') - julianday(signed_on) <= 30 THEN 'new'
            WHEN spend >= 1000                                       THEN 'vip'
            WHEN julianday('2026-08-23') - julianday(last_order) > 90 THEN 'at risk'
            ELSE 'active'
       END AS segment
FROM customers;

Hale comes back as new. Not because Hale is more new than VIP, but because the new line is above the VIP line. That is the whole mechanism, and everything else on this page follows from it.

Two conditions that can both be true for one row are called overlapping. Two conditions that cannot are called mutually exclusive. Bands built off one column, such as under 500 and 500 or more, are mutually exclusive, and their order genuinely does not matter. Rules built off different columns almost always overlap.

Move one line, and the VIP count doubles

First: if the VIP rule moves to the top, how many of the ten customers change segment?

Two of them, Alvarez and Hale, and that is enough to change every number a stakeholder reads. Here are both orderings counted, same data, same three rules.

The total is 10 both times, which is exactly why this survives review. Nothing is missing, nothing is duplicated, and the sum reconciles. A reconciliation check cannot see this problem at all, because both answers are internally consistent. Only the split moved.

Say this one out loud before reading on: if the VIP count doubled and the total did not change, where did the two extra VIPs come from? They came from the new segment, and they were always both things.

The customer both versions hide

Before the answer: Ellis has spent 2100 and last ordered on 11 February, over six months ago. What does Ellis get called?

VIP, in both orderings. Ellis is also at risk, and no version of this labelled query will ever say so, because the VIP line comes before the at risk line in both. A high spending customer who has stopped ordering is the single most useful row in this table, and the segmentation deletes that fact.

This is what a label costs. One column can hold one answer, so the moment your rules overlap, a label is a decision about which fact survives. Write the rules as separate columns and nothing has to be thrown away.

SELECT name,
       CASE WHEN julianday('2026-08-23') - julianday(signed_on) <= 30 THEN 1 ELSE 0 END AS is_new,
       CASE WHEN spend >= 1000                                        THEN 1 ELSE 0 END AS is_vip,
       CASE WHEN julianday('2026-08-23') - julianday(last_order) > 90 THEN 1 ELSE 0 END AS is_at_risk
FROM customers;

Three rows carry two 1s: Alvarez, Ellis and Hale. Those are the three rows the labelled query had to choose for, and now you can see them instead of guessing. Ellis, the churning big spender, is one line of SQL away: WHERE is_vip = 1 AND is_at_risk = 1.

The flags also add up honestly. Four customers are VIPs by the rule, whichever ordering you use. The label said 2 in one version and 4 in the other because a label is a ranking of your rules, not a measurement of your customers.

The branch that can never run

First: which of these two labels will never appear in the output?

SELECT name, spend,
       CASE WHEN spend >= 100 THEN 'over 100'
            WHEN spend >= 500 THEN 'over 500'
            ELSE 'small'
       END AS band
FROM customers;

over 500, and the query runs clean. Every customer over 500 is also over 100, so the first line takes all of them. Eight rows come back as over 100, two as small, and over 500 is zero rows. The label exists in the SQL and never once reaches the screen.

This is the same overlap in its most obvious form, and it has an easy detection: count the rows per label. A label with zero rows is either a rule nobody in your data meets, which is worth knowing, or a rule an earlier line made unreachable, which is a defect. Both are things you want to find before somebody asks why the report has no premium tier.

SELECT segment, COUNT(*) AS rows_labelled
FROM (SELECT ..., CASE ... END AS segment FROM customers)
GROUP BY segment
ORDER BY rows_labelled;

How to test any CASE in two minutes

Three checks, and none of them needs a tool you do not already have.

1. Count the rows per label. A zero means an unreachable branch or a rule nobody meets. A label holding almost everything usually means a condition that is looser than intended.
2. Rewrite the rules as flags and count the overlaps. SELECT COUNT(*) FROM (...) WHERE is_new + is_vip + is_at_risk > 1 returns 3 on this table. Anything above zero means the order of your WHEN lines is deciding outcomes.
3. Move the lines and rerun. If the counts change, you have the proof. If they do not, your conditions are mutually exclusive and you can stop worrying about the order in this query.

Then write the priority down in the query itself, because it is the part no future reader can recover from the code. A comment naming the order and the reason costs one line and saves the argument. The format is in how to comment SQL so it teaches.

/* WHY: Priority order is deliberate. A customer who is both new and a VIP is
   counted as new, because the onboarding campaign owns them for 30 days.
   3 of 10 customers meet more than one rule, so this order is the decision. */

Picture the last segmentation you shipped. Two of its rules, side by side: can one customer meet both? If you cannot answer that from memory, that is the query to run the flag check on tomorrow.

Edge cases worth knowing

NULL never matches anything, so it lands in the ELSE. Jansen has no spend recorded. In the band example, NULL >= 100 is neither true nor false, so Jansen falls through to small, which reads as "spends very little" and actually means "we do not know". Give unknown its own branch: WHEN spend IS NULL THEN 'unknown', first, before anything that tests the column. There is more on this behaviour in NULL in SQL.

No ELSE means NULL, silently. A CASE that runs out of conditions returns NULL for that row rather than raising anything. Then GROUP BY gives you a blank segment in the report. Always write the ELSE, even when you believe it is unreachable, and put something readable in it like 'unclassified'.

BETWEEN bands overlap at the seam. BETWEEN 0 AND 10 and BETWEEN 10 AND 20 both contain 10. In a CASE that does not double count, it just quietly hands 10 to the first band. Write bands with one inclusive end, such as < 10 then < 20, and the seam stops being a decision.

Cut-offs that live in the SQL have to be changed in the SQL. If the VIP threshold moves to 1200 next quarter, every query carrying that number needs finding. Once more than two or three queries share a rule set, put the bands in a small table and join to it instead.

The first-match rule is standard, not an engine quirk. PostgreSQL, MySQL, SQL Server, SQLite and Oracle all evaluate WHEN clauses in order and stop at the first true one. Rewriting the same CASE on a different engine will not change what it does.

Why it works this way

CASE is not a set of rules being weighed. It is a chain of if statements, and the first true one returns.

That design is deliberate, and it is what makes CASE safe to use for guarding against errors. Because evaluation stops at the first true branch, you can write WHEN denominator = 0 THEN NULL WHEN ... THEN total / denominator and the division never runs on the zero. If SQL evaluated every branch and then picked, that pattern would fail.

The cost of the same design is the subject of this page. A structure that stops early cannot tell you what it skipped. Nothing in the language knows that your three rules were meant to describe three different kinds of customer, so nothing can warn you when one customer is two of them. That check is yours, and the flag columns are how you run it.

How to apply this to your own work

1. Open the CASE you use most. For each pair of conditions, ask whether one row can satisfy both.
2. If any pair can, rewrite the rules as flag columns once and count the rows with more than one flag set.
3. Decide which fact wins, with the person who owns the metric, not alone at your desk.
4. Write that decision as a comment above the CASE, naming the priority and the reason.
5. Keep the flag version. Labels are for reports, flags are for questions, and the flag query is where the interesting rows live.
6. Do not retrofit every CASE in the codebase this week. Start with the ones whose numbers get published.

If you have paper nearby, draw two overlapping circles for any two rules from your own work and write in the middle what kind of customer sits there. If that space has a name, it probably deserves its own label.

Cheat sheet

The one habit to keep

Before you write a CASE, take any two of its conditions and ask whether one row can meet both. If the answer is yes, you are not writing a classification. You are writing a priority list, and priority lists belong to the business, not to whoever typed the query.

Which segment in your reporting is defined by a rule that somebody else in the company defines differently, and does anyone know which order your query applies them in?