MySQL IF Statement

Master the MySQL IF statement for stored programs — IF/THEN, IF/THEN/ELSE, and IF/THEN/ELSEIF/ELSE forms. Covers DELIMITER usage, OUT parameters, the IF statement vs the IF() function, common variable-naming bugs, and best practices for branching inside MySQL stored procedures.

The IF statement in MySQL stored programs lets you branch — run one block of code if a condition is true, optionally run a different block otherwise. It only works inside stored procedures, functions, triggers, and events; you can't write a bare IF at the top level of a script.

The IF statement comes in three forms, in increasing order of complexity:

All examples on this page use a small employees table. Run this once to set it up:

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    employee_id     INT UNSIGNED PRIMARY KEY,
    first_name      VARCHAR(20),
    last_name       VARCHAR(25)    NOT NULL,
    salary          DECIMAL(10, 2) NOT NULL,
    department_id   INT
);

INSERT INTO employees VALUES
    (100, 'Steven',    'King',      24000.00,  9),
    (101, 'Neena',     'Kochhar',   17000.00,  9),
    (102, 'Lex',       'De Haan',   17000.00,  9),
    (103, 'Alexander', 'Hunold',     9000.00,  6),
    (104, 'Bruce',     'Ernst',      6000.00,  6),
    (105, 'David',     'Austin',     4800.00,  6),
    (106, 'Valli',     'Pataballa',  4800.00,  6),
    (107, 'Diana',     'Lorentz',    4200.00,  6),
    (108, 'Nancy',     'Greenberg', 12000.00, 10),
    (109, 'Daniel',    'Faviet',     9000.00, 10);

The simplest form — run a block if a condition is true:

This procedure looks up an employee's salary and sets the output to 'high' only when the salary exceeds 17,000:

DROP PROCEDURE IF EXISTS emplevel;

DELIMITER $$

CREATE PROCEDURE emplevel(IN  emp_id INT,
                          OUT sal    VARCHAR(20))
BEGIN
    DECLARE sal_range DECIMAL(10, 2) DEFAULT 0;

    SELECT salary INTO sal_range
    FROM   employees
    WHERE  employee_id = emp_id;

    IF sal_range > 17000 THEN
        SET sal = 'high';
    END IF;
END$$

DELIMITER ;

-- Test it
CALL emplevel(100, @level);
SELECT @level;

Steven King's salary is 24,000, which is > 17,000, so sal got set to 'high'. If you call emplevel(104, @level) instead — Bruce Ernst at 6,000 — @level will come back as NULL, because the IF was false and we never assigned anything to the OUT parameter. That's the limitation of plain IF ... THEN: the falsy case does nothing.

Add an ELSE branch to handle the falsy case:

DROP PROCEDURE IF EXISTS emplevel2;

DELIMITER $$

CREATE PROCEDURE emplevel2(IN  emp_id INT,
                           OUT sal    VARCHAR(20))
BEGIN
    DECLARE sal_range DECIMAL(10, 2) DEFAULT 0;

    SELECT salary INTO sal_range
    FROM   employees
    WHERE  employee_id = emp_id;

    IF sal_range > 17000 THEN
        SET sal = 'high';
    ELSE
        SET sal = 'low';
    END IF;
END$$

DELIMITER ;

CALL emplevel2(104, @level);    -- Bruce Ernst — salary 6000
SELECT @level;

Bruce earns 6,000, which fails the > 17000 check, so the ELSE branch ran and we got 'low'.

For more than two branches, chain ELSEIF conditions. They're tested in order — the first one that matches wins, and any later ones are skipped:

DROP PROCEDURE IF EXISTS emplevel3;

DELIMITER $$

CREATE PROCEDURE emplevel3(IN  emp_id INT,
                           OUT sal    VARCHAR(20))
BEGIN
    DECLARE sal_range DECIMAL(10, 2) DEFAULT 0;

    SELECT salary INTO sal_range
    FROM   employees
    WHERE  employee_id = emp_id;

    IF sal_range > 17000 THEN
        SET sal = 'high';
    ELSEIF sal_range >= 12000 THEN
        SET sal = 'avg';
    ELSE
        SET sal = 'low';
    END IF;
END$$

DELIMITER ;

CALL emplevel3(108, @level);    -- Nancy Greenberg — salary 12000
SELECT @level;

Nancy earns 12,000. The first IF (> 17000) fails, the ELSEIF (>= 12000) succeeds, so we get 'avg'. Notice we don't need to write sal_range >= 12000 AND sal_range <= 17000 — because branches are checked in order, anything reaching the ELSEIF has already failed the first IF, so we know it's not above 17000.

You'll see DELIMITER $$ ... DELIMITER ; wrapping every procedure on this page. Here's why:

The MySQL client uses ; to mark "send this to the server now." Inside a procedure body, semicolons mark the ends of internal statements like SET sal = 'high'; — but you don't want the client cutting your procedure in half there. DELIMITER $$ temporarily changes the "send now" character to $$, so the inner semicolons stay quiet until the final END$$. Then DELIMITER ; sets it back.

1. Always include an ELSE branch when the OUT parameter or working variable matters. Otherwise you can leave it NULL by accident.
2. Use DROP PROCEDURE IF EXISTS at the top of your script — makes deployment scripts idempotent and easy to re-run.
3. Quote string literals — 'high', not high. Unquoted bareword identifiers will resolve as column names and silently misbehave.
4. Watch the variable scope. Local variables (declared with DECLARE) and column names share a namespace inside the procedure — if your variable has the same name as a column, you'll get unexpected results. Prefix or rename to disambiguate.
5. Order conditions from most-specific to least-specific. The first match wins, so > 17000 must come before >= 12000, not the other way around.

• The IF statement controls branching inside MySQL stored procedures, functions, triggers, and events — three forms: IF ... THEN, IF ... THEN ... ELSE, IF ... THEN ... ELSEIF ... ELSE.
• Use ELSEIF (one word) for multi-way branching; conditions are evaluated in order, first match wins.
• Don't confuse the IF statement (control flow) with the IF() function (returns a value in any expression).
• Wrap procedure bodies in DELIMITER $$ ... $$ when running through the mysql CLI; GUI clients handle this automatically.
• Always quote string literals, always include END IF;, and prefer an explicit ELSE branch to avoid silent NULLs.