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

Try it in the SQL analyzer. If you want to be on the safe side and not have to trust the database to work one way (and not to change that behavior ever in new versions), just make two queries and do the IF programmatically.

Answer 2

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 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 3

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!