Does MySQL Short Circuit the IF() function?

Question

I need to query data from a second table, but only if a rare set of conditions in the primary table is met:

SELECT ..., IF(a AND b AND c AND (SELECT 1 FROM tableb ...)) FROM tablea ...

a, b, and c conditions are almost always false, so my thinking is the subquery will never execute for most rows in the result set and thus be way faster than a join. But that would only true if the IF() statement short circuits.

Does it?

Thanks for any help you guys can provide.

Answer 1

The answer is YES.
The IF(cond,expr_true,expr_false) within a mysql query is short-circuited.

Here a test, using @variables to prove the fact:

SET @var:=5;  
SELECT IF(1 = 0, (@var:=@var + 1), @var ); -- using ':=' operator to modify 'true' expr @var 
SELECT IF(1 = 1, @var, (@var:=@var + 1) ); -- using ':=' operator to modify 'false' expr @var 
SELECT @var;

The result is '5' from all three SELECT queries.

Had the IF() function NOT short circuited, the result would be a '5' from SELECT #1, and '6' from SELECT #2, and a '7' from the last "select @var".

This is because the 'true' expression is NEVER executed, in select #1 and nor is the false expression executed for select #2.

Note the ':=' operator is used to modify an @var, within an SQL query (select,from, and where clauses). You can get some really fancy/complex SQL from this. I've used @vars to apply 'procedural' logic within a SQL query.

-- J Jorgenson --

Answer 2

With J. Jorgenson's help I came up with my own test case. His example does not try to short circuit in the condition evaluation, but using his idea I came up with my own test and verified that MySQL does indeed short-circuit the IF() condition check.

SET @var:=5;
SELECT IF(1 = 0 AND (@var:=10), 123, @var); #Expected output: 5
SELECT IF(1 = 1 AND (@var:=10), @var, 123); #Expected output: 10

On the second example, MySQL is properly short-circuiting: @var never gets set to 10.

Thanks for the help J. Jorgenson!