SQL ELSEIF

What does SQL ELSEIF do?

ELSEIF adds an additional conditional branch inside an IF ... END IF block within stored programs.

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 ELSEIF: Supported: MySQL, MariaDB. Similar but differently spelled constructs exist in Oracle (ELSIF), PostgreSQL PL/pgSQL (ELSIF), SQL Server (ELSE IF). Not in Standard SQL.

SQL ELSEIF Full Explanation

ELSEIF is a control-flow keyword available in MySQL stored procedures, functions, triggers, and events. It lets you evaluate multiple mutually exclusive Boolean expressions without nesting separate IF blocks. Execution starts with the first IF condition; if it evaluates to TRUE the associated statement_list runs and execution jumps to END IF. If it is FALSE, control passes to the first ELSEIF. Each ELSEIF is checked in order. When a TRUE condition is found its statement_list runs and the block exits. If no IF or ELSEIF conditions match, the optional ELSE clause runs. Only one branch executes. ELSEIF may not be used in plain SQL outside stored program contexts. Different vendors spell the keyword differently (e.g., ELSIF in Oracle/PostgreSQL, ELSE IF in SQL Server) so portability requires care.

SQL ELSEIF Syntax

IF condition1 THEN
   statement_list1;
ELSEIF condition2 THEN
   statement_list2;
ELSEIF condition3 THEN
   statement_list3;
ELSE
   statement_list_else;
END IF;

SQL ELSEIF Parameters

conditionX (Boolean) - Expression that returns TRUE or FALSE
statement_listX - SQL statements to execute when its condition is TRUE

Example Queries Using SQL ELSEIF

DELIMITER //
CREATE PROCEDURE set_user_tier(p_points INT, OUT p_tier VARCHAR(10))
BEGIN
   IF p_points >= 1000 THEN
       SET p_tier = 'platinum';
   ELSEIF p_points >= 500 THEN
       SET p_tier = 'gold';
   ELSEIF p_points >= 100 THEN
       SET p_tier = 'silver';
   ELSE
       SET p_tier = 'bronze';
   END IF;
END;//
DELIMITER ;

Expected Output Using SQL ELSEIF

When the procedure runs, p_tier holds the tier label that matches the first TRUE condition
Only one SET executes

Use Cases with SQL ELSEIF

Classifying numeric ranges into categories inside a procedure
Implementing multi-branch business rules without deeply nested IF statements
Replacing chained CASE expressions when procedural flow is clearer

Common Mistakes with SQL ELSEIF

Using ELSEIF in plain SELECT queries (it only works in stored programs)
Forgetting END IF which raises a syntax error
Confusing ELSEIF with the IF() expression function
Assuming ELSEIF is portable; other dialects use ELSIF or ELSE IF

First Introduced In

MySQL 4.0 (stored procedures became GA in 5.0)

Frequently Asked Questions

What is the difference between ELSEIF and ELSIF?

Oracle and PostgreSQL spell the keyword ELSIF, while MySQL and MariaDB use ELSEIF. Functionally they are the same, but the spelling must match the dialect.

Can ELSEIF replace nested IF statements?

Yes. Multiple ELSEIF clauses flatten nested logic, making code easier to read and maintain.

Does ELSEIF affect query performance?

In stored programs the overhead is negligible. MySQL evaluates conditions sequentially until one matches - early matches exit sooner.

What happens if none of the conditions are true and there is no ELSE?

The IF block ends without executing any statements. No error is raised.