SQL EXIT

What is the SQL EXIT statement?

EXIT immediately breaks out of the current (or labeled) loop or block in procedural SQL.

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 EXIT: Supported: PostgreSQL (PL/pgSQL), Oracle (PL/SQL), IBM Db2 (compound SQL), MariaDB 10.3+ (procedures). Not supported: Standard SQL, MySQL (uses LEAVE).

SQL EXIT Full Explanation

EXIT is a control-flow statement available in procedural SQL dialects such as PostgreSQL’s PL/pgSQL and Oracle’s PL/SQL. When executed, it stops further iteration of the innermost loop (WHILE, FOR, LOOP) or the explicitly named block and continues execution with the first statement following that loop or block. If a WHEN clause is supplied, EXIT executes only when the Boolean condition evaluates to TRUE, enabling early termination based on runtime logic. Using labels lets developers exit outer loops without using auxiliary flags or complex logic.EXIT does not commit or roll back transactions and should not be confused with client-side commands like QUIT or with RETURN, which leaves a function entirely. It is evaluated at runtime, so unreachable EXIT statements are syntactically allowed but never executed. Misusing EXIT in plain SQL (outside a procedural block) raises a syntax error.

SQL EXIT Syntax

EXIT [ label ] [ WHEN boolean_condition ];

SQL EXIT Parameters

Example Queries Using SQL EXIT

-- Example 1: basic break out of a loop
DO $$
DECLARE
    i int := 0;
BEGIN
    LOOP
        i := i + 1;
        RAISE NOTICE 'i=%', i;
        IF i > 3 THEN
            EXIT; -- leaves the loop
        END IF;
    END LOOP;
END$$;

-- Example 2: exit outer loop with a label
DO $$
DECLARE
    r record;
BEGIN
    <<outer>>
    FOR r IN SELECT * FROM generate_series(1,5) AS id LOOP
        FOR i IN 1..10 LOOP
            IF i = 4 THEN
                EXIT outer; -- leaves both loops
            END IF;
        END LOOP;
    END LOOP;
END$$;

-- Example 3: conditional exit
DO $$
DECLARE
    total int := 0;
BEGIN
    FOR i IN 1..100 LOOP
        total := total + i;
        EXIT WHEN total > 50;
    END LOOP;
    RAISE NOTICE 'total=%', total; -- 55
END$$;

Expected Output Using SQL EXIT

The loop or labeled block stops executing immediately
Control moves to the first statement that follows the exited construct
No rows are returned unless additional statements produce output

Use Cases with SQL EXIT

Breaking out of an infinite or long-running loop once a condition is met
Exiting nested loops cleanly using labels
Replacing complex IF/ELSE logic with simpler early termination
Improving performance by stopping unnecessary iterations once a result is found

Common Mistakes with SQL EXIT

Attempting to use EXIT in plain SQL outside a procedural block
Forgetting to specify a label when trying to exit an outer loop, resulting in only the inner loop terminating
Confusing EXIT with RETURN, which exits the whole function
Assuming EXIT commits or rolls back a transaction

First Introduced In

PostgreSQL 7.0 (PL/pgSQL)

Frequently Asked Questions

What is the difference between EXIT and RETURN?

EXIT leaves the current loop or labeled block and continues executing the remaining code in the function. RETURN exits the entire function or procedure immediately and optionally supplies a return value.

Can I exit multiple nested loops at once?

Yes. Provide a label on the outer loop or block and reference that label in the EXIT statement (e.g., EXIT outer_loop;).

Does EXIT commit my transaction?

No. EXIT only affects control flow inside a procedural block. Transaction control must be handled separately with COMMIT or ROLLBACK.

Is EXIT available in MySQL?

Not under that name. MySQL uses LEAVE to accomplish the same task inside stored programs.