SQL ELSE

What is the SQL ELSE keyword used for?

ELSE supplies a default result or code branch when preceding CASE WHEN or IF conditions evaluate to false.

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 ELSE: ANSI SQL (CASE expression), PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, DB2

SQL ELSE Full Explanation

ELSE is not a standalone SQL statement. It is a reserved keyword that finishes a conditional construct by defining what should happen when none of the earlier conditions are satisfied.1. In a CASE expression (ANSI SQL) - Appears once, after all WHEN clauses and before END. - If omitted, the CASE expression returns NULL when no WHEN matches. - Data type of the ELSE result must be compatible with the THEN results.2. In procedural control-flow blocks (dialect specific) - Used with IF … ELSE … END IF (MySQL, PL/pgSQL, T-SQL) or BEGIN … END blocks. - Marks the alternate set of statements executed when the IF condition is false.Caveats- Only one ELSE clause is allowed per CASE or IF.- In SELECT lists, keep ELSE expressions side-effect free.- In some engines, ELSE cannot contain another control-flow statement inside a CASE expression but can inside procedural IF blocks.

SQL ELSE Syntax

-- CASE expression (ANSI SQL)
CASE
    WHEN <condition1> THEN <result1>
    WHEN <condition2> THEN <result2>
    ELSE <default_result>
END

-- Procedural IF block (dialect specific example)
IF <condition> THEN
    <statement_list1>;
ELSE
    <statement_list2>;
END IF;

SQL ELSE Parameters

Example Queries Using SQL ELSE

-- 1. CASE with ELSE in a SELECT
SELECT
    order_id,
    CASE
        WHEN total_amount >= 1000 THEN 'High'
        WHEN total_amount >= 500  THEN 'Medium'
        ELSE 'Low'
    END AS spend_category
FROM orders;

-- 2. Procedural IF…ELSE (PostgreSQL PL/pgSQL)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM users WHERE email = 'admin@example.com') THEN
        INSERT INTO users(email, role) VALUES('admin@example.com', 'admin');
    ELSE
        RAISE NOTICE 'Admin user already exists';
    END IF;
END$$;

Expected Output Using SQL ELSE

Each order row gains a spend_category of High, Medium, or Low. Rows that do not satisfy any WHEN condition return 'Low'.
The DO block inserts a new admin user only if absent; otherwise it prints a notice.

Use Cases with SQL ELSE

Provide a fallback value in derived columns.
Prevent NULL results when no WHEN conditions match.
Branch program logic in stored procedures or scripts.
Simplify nested IF logic by consolidating defaults.

Common Mistakes with SQL ELSE

Omitting ELSE and expecting a non-NULL result.
Using more than one ELSE clause in the same CASE/IF.
Mixing incompatible data types between THEN and ELSE expressions.
Forgetting END or END IF delimiter after ELSE.

First Introduced In

SQL-92 (optional feature F511)

Frequently Asked Questions

What happens if I omit ELSE in a CASE expression?

The expression returns NULL when no WHEN condition matches, which may propagate NULLs in your result set.

Can ELSE return a different data type than THEN clauses?

All THEN and ELSE expressions must be implicitly convertible to a common data type in the target database; otherwise, a type mismatch error occurs.

Is ELSE allowed in a simple CASE expression?

Yes. Whether you use simple or searched CASE syntax, ELSE behaves the same and supplies the default value.

Can I nest CASE expressions inside an ELSE clause?

Yes. CASE can be nested within THEN or ELSE to model complex logic, but readability can suffer. Prefer breaking logic into separate computed columns or CTEs.