IF ELSE statement

The IF ELSE statement can be used as a main statement, or within a parent statement, to return a value depending on whether a condition, or a series of conditions match. The statement allows for multiple ELSE IF expressions, and a final ELSE expression, with no limit to the number of ELSE IF conditional expressions.

Note

As THROW, CONTINUE, and BREAK do not return an expression, they must be inside a separate code block inside an IF ELSE statement.

The original IF ELSE syntax involves one or more conditions, THEN, an expression if the condition evaluates to true, and the END keyword to finalize it. A separate code block can also be used, in which case multiple statements can be run.

SurrealQL legacy syntax
IF @condition THEN @expression | { @expression; .. }
   [ ELSE IF @condition THEN @expression | { @expression; .. } ] ...
   [ ELSE @expression | { @expression; .. } ]
END;

The modern IF ELSE syntax does not require THEN or END, instead using {} to open up a code block on each condition check which will be run when it evaluates to true.

Modern syntax
IF @condition { @expression; .. }
   [ ELSE IF @condition { @expression; .. } ] ...
   [ ELSE { @expression; .. } ]

Example usage

Basic usage

The following queries show example usage of this statement.

The smallest possible IF THEN statement simply does something when a condition is true, and nothing otherwise.

// Original syntax
IF 9 = 9 THEN RETURN "Nine is indeed nine" END;

// New scope syntax
IF 9 = 9 { RETURN 'Nine is indeed nine' };

As the last line of a scope is its return value, the RETURN keyword can also be placed before the entire IF THEN statement. This is particularly convenient in long IF ELSE chains to avoid using the RETURN keyword at the end of every check for a condition.

LET $num = 100;

RETURN IF $num < 0 {
    "Negative"
} ELSE IF $num = 0 {
    "Zero"
} ELSE IF $num = 13 {
    "Thirteen"
} ELSE {
    "Positive uninteresting number"
};

The THROW keyword inside {} braces can be used to break out of an IF LET statement early.

LET $badly_formatted_datetime = "2024-04TT08:08:08Z";
// Original syntax
IF !type::is::datetime($badly_formatted_datetime) THEN {
    THROW "Whoops, that isn't a real datetime"
} END;

// New scope syntax
IF !type::is::datetime($badly_formatted_datetime) {
    THROW "Whoops, that isn't a real datetime"
};

Response
"An error occurred: Whoops, that isn't a real datetime"

ELSE IF branches and a final ELSE can be added into an IF ELSE statement:

// Original syntax
IF $access = "admin" THEN
	SELECT * FROM account
ELSE IF $access = "user" THEN
	SELECT * FROM $auth.account
ELSE {
    THROW "Access method hasn't been defined!"
}
END;

// New scope syntax
RETURN
    IF $access = "admin" { (SELECT * FROM account) }
    ELSE IF $access = "user"  { (SELECT * FROM $auth.account) }
    ELSE { THROW "Access method hasn't been defined!" };

Advanced usage

The output of an IF ELSE statement can be assigned to a parameter:

LET $num = 9;

// Original syntax
LET $odd_even =
  IF $num % 2 = 0 THEN
    "even"
  ELSE
    "odd"
  END;

// New scope syntax
LET $odd_even = 
    IF $num % 2 = 0 { "even" } 
    ELSE { "odd" };

If-else statements can also be used as subqueries within other statements.

UPSERT person SET railcard =
	(IF age <= 10 THEN)
		'junior'
	ELSE IF age <= 21 THEN
		'student'
	ELSE IF age >= 65 THEN
		'senior'
	ELSE
		NULL
	END
;

Alternatively:

UPSERT person SET railcard =
    (IF age <= 10 { 'junior' })
    ELSE IF age <= 21 { 'student' }
    ELSE IF age >= 65 { 'senior' }
    ELSE { NULL };

You can also have nested conditions:

IF $access = "admin" THEN
    SELECT * FROM admin_data WHERE access_level = 'full'
ELSE IF $access = "user" THEN
    IF $auth.role = "premium" THEN
        IF $auth.subscription_status = "active" THEN
            SELECT * FROM premium_user_data WHERE active = 1
        ELSE IF $auth.subscription_status = "trial" THEN
            SELECT * FROM trial_user_data
        ELSE
            SELECT * FROM basic_user_data
        END
    ELSE IF $auth.role = "standard" THEN
        SELECT * FROM standard_user_data WHERE region = 'US'
    ELSE IF $auth.role = "standard" AND $auth.subscription_status = "active" THEN
        SELECT * FROM standard_user_data WHERE region = 'EU'
    ELSE
        SELECT * FROM unauthorized_user_data
    END
ELSE
    SELECT * FROM unknown_access_data
END;

The new scope syntax makes it easy to carry out multiple statements even inside nested conditions.

IF $access = 'admin'
	{
        CREATE admin_user_event SET 
            time = time::now(),
            info = "Admin user activity registered";
		SELECT * FROM admin_data WHERE access_level = 'full';
	}
ELSE IF $access = 'user'
	{
		IF $auth.role = 'premium'
			{
                CREATE premium_user_event SET 
                    time = time::now(),
                    info = "Premium user activity registered";

				IF $auth.subscription_status = 'active'
					{ SELECT * FROM premium_user_data WHERE active = 1 }
				ELSE IF $auth.subscription_status = 'trial'
					{ SELECT * FROM trial_user_data }
				ELSE
					{ SELECT * FROM basic_user_data }
			}
		ELSE IF $auth.role = 'standard'
			{ SELECT * FROM standard_user_data WHERE region = 'US' }
		ELSE IF $auth.role = 'standard' AND $auth.subscription_status = 'active'
			{ SELECT * FROM standard_user_data WHERE region = 'EU' }
		ELSE
			{ SELECT * FROM unauthorized_user_data }
	}
ELSE
	{ SELECT * FROM unknown_access_data }