SQL LEAVE

What is the SQL LEAVE statement?

Immediately exits a labeled loop or BEGIN...END block inside a MySQL stored program.

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 LEAVE: Supported: MySQL, MariaDB Not supported: PostgreSQL, SQL Server, Oracle, SQLite (use their own EXIT/BREAK equivalents)

SQL LEAVE Full Explanation

LEAVE is a control-flow statement available in MySQL and MariaDB stored procedures, functions, triggers, and events. When executed, it terminates the innermost statement block or loop that has the specified label and transfers control to the first statement following that block. LEAVE works with LOOP, WHILE, REPEAT, and unlabeled BEGIN...END sections, provided each block is explicitly labeled. Unlike ITERATE, which restarts the loop, LEAVE behaves like a break statement in conventional languages. It does not implicitly roll back data changes; any modifications remain unless a ROLLBACK is issued or the routine ends with an unhandled error. LEAVE can only reference labels defined within the same stored program level, and the label name must be unique within that scope.

SQL LEAVE Syntax

label_name: LOOP
    -- statements
    IF condition THEN
        LEAVE label_name;  
    END IF;
END LOOP label_name;

SQL LEAVE Parameters

Example Queries Using SQL LEAVE

DELIMITER //
CREATE PROCEDURE demo_leave()
BEGIN
    DECLARE counter INT DEFAULT 0;

    my_loop: LOOP
        SET counter = counter + 1;
        IF counter >= 5 THEN
            LEAVE my_loop;   -- exit the loop when counter hits 5
        END IF;
    END LOOP my_loop;

    SELECT counter AS final_value;  -- returns 5
END //
DELIMITER ;

Expected Output Using SQL LEAVE

The procedure runs, exits the loop once counter equals 5, and returns a single row with final_value = 5

Use Cases with SQL LEAVE

Break out of a LOOP, WHILE, or REPEAT when a terminating condition occurs
Exit an inner block early after validating parameters or detecting an error
Improve readability by avoiding deeply nested IF statements

Common Mistakes with SQL LEAVE

Forgetting to label the target loop, causing a syntax error
Using LEAVE outside a stored program context (not allowed in plain SQL)
Assuming LEAVE rolls back data; it only changes control flow
Reusing the same label name in nested blocks, leading to ambiguity

First Introduced In

MySQL 5.0

Frequently Asked Questions

What does LEAVE do in a stored procedure?

LEAVE immediately terminates the labeled loop or BEGIN...END block and resumes execution after that block.

Do I need to label every loop to use LEAVE?

Yes. LEAVE requires an explicit label that matches the target loop or block. Omitting the label triggers a syntax error.

How is LEAVE different from ITERATE?

LEAVE exits the loop entirely, while ITERATE skips the remaining statements in the current iteration and restarts the loop.

Can LEAVE be used outside stored routines?

No. LEAVE is valid only inside stored procedures, functions, triggers, or events in MySQL or MariaDB.