SQL THEN

What is the SQL THEN keyword?

THEN specifies the result returned when a WHEN condition in a CASE expression (or IF statement in procedural SQL) evaluates to true.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL THEN:

SQL THEN Full Explanation

THEN is not a stand-alone statement; it is a clause that appears inside conditional constructs. In Standard SQL it is used in CASE expressions to map each WHEN condition to its corresponding result. Some procedural dialects (PL/pgSQL, T-SQL, PL/SQL, MySQL stored programs) also use THEN in IF … THEN … END IF blocks. The SQL engine evaluates conditions top-down; on the first true WHEN (or IF) it executes or returns the expression following THEN and skips the remaining branches. If no conditions match, control passes to ELSE or raises NULL where ELSE is optional. THEN can return scalars, subqueries, expressions, or execute statement blocks depending on context. Although trivial in appearance, correct use of THEN ensures deterministic branching, readable code, and prevents fall-through errors.

SQL THEN Syntax

-- Searched CASE expression
CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result_default
END

-- IF statement in procedural SQL (example: PL/pgSQL)
IF condition THEN
    statements;
ELSIF condition2 THEN
    statements;
ELSE
    statements;
END IF;

SQL THEN Parameters

Example Queries Using SQL THEN

-- 1. Categorize order value
SELECT order_id,
       total_amount,
       CASE
           WHEN total_amount >= 1000 THEN 'High'
           WHEN total_amount >= 100 THEN 'Medium'
           ELSE 'Low'
       END AS order_size
FROM   orders;

-- 2. Procedural example (T-SQL)
BEGIN
    IF @error_count > 0 THEN
        RAISERROR ('Errors found', 16, 1);
    ELSE
        PRINT 'All good';
    END IF;
END;

Expected Output Using SQL THEN

Example 1 returns one row per order plus a derived column order_size containing High, Medium, or Low according to the thresholds
Example 2 raises an error if @error_count is positive or prints All good otherwise

Use Cases with SQL THEN

Map numeric or date ranges to descriptive labels
Replace nested IF statements with clear CASE logic
Control flow inside stored procedures and triggers
Implement derived metrics directly in SELECT lists
Return different aggregates in GROUP BY queries with conditional logic

Common Mistakes with SQL THEN

Writing THEN outside CASE or IF blocks
Omitting THEN after WHEN, leading to syntax errors
Mixing simple CASE (which compares to one expression) with searched CASE without adjusting WHEN clauses
Forgetting an ELSE branch and assuming NULL will not propagate

First Introduced In

SQL-92

Frequently Asked Questions

What does THEN do in SQL?

THEN specifies the result or statement block that should run when the associated condition in a CASE or IF construct is true.

Is ELSE required when using THEN?

No. ELSE is optional, but without it unmatched conditions return NULL or do nothing, which can lead to unexpected results.

Can THEN return a subquery?

Yes. The expression after THEN can be a scalar subquery as long as it returns exactly one value compatible with the CASE result type.

How is THEN different in procedural SQL?

In stored procedures THEN begins a block of one or more statements executed when the IF condition is true, whereas in plain SQL it returns a single expression.