SQL Server Conditional Flow

If I write two SELECT statements in a IF EXISTS condition with a AND clause in between these select queries, does both queries get executed even if the first SELECT returns false?

IF EXISTS (SELECT....) AND EXISTS(SELECT ....)
BEGIN

END

Does the SQL Server Engine execute both the SQL Statement in this scenario?

Thanks Krish

Solution 1:

I would rewrite the test as

IF CASE
     WHEN EXISTS (SELECT ...) THEN CASE
                                   WHEN EXISTS (SELECT ...) THEN 1
                                 END
   END = 1  

This guarantees short circuiting as described here but does mean you need to select the cheapest one to evaluate up front rather than leaving it up to the optimiser.

In my extremely limited tests below the following seemed to hold true when testing

1. EXISTS AND EXISTS

The EXISTS AND EXISTS version seems most problematic. This chains together some outer semi joins. In none of the cases did it re-arrange the order of the tests to try and do the cheaper one first (an issue discussed in the second half of this blog post). In the IF ... version it wouldn't have made any difference if it had as it did not short circuit. However when this combined predicate is put in a WHERE clause the plan changes and it does short circuit so that rearrangement could have been beneficial.

/*All tests are testing "If False And False"*/

IF EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
AND EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)
PRINT 'Y'
/*
Table 'spt_values'. Scan count 1, logical reads 9
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

IF EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1) 
AND EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2) 
PRINT 'Y'
/*
Table 'spt_monitor'. Scan count 1, logical reads 1
Table 'spt_values'. Scan count 1, logical reads 9
*/

SELECT 1
WHERE  EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
AND EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

SELECT 1
WHERE  EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1) 
AND EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2) 
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_values'. Scan count 1, logical reads 9

*/

The plans for all these appear very similar. The reason for the difference in behaviour between the SELECT 1 WHERE ... version and the IF ... version is that for the former one if the condition is false then the correct behaviour is to return no result so it just chains the OUTER SEMI JOINS and if one is false then zero rows carry forward to the next one.

However the IF version always needs to return a result of 1 or zero. This plan uses a probe column in its outer joins and sets this to false if the EXISTS test is not passed (rather than simply discarding the row). This means that there is always 1 row feeding into the next Join and it always gets executed.

The CASE version has a very similar plan but it uses a PASSTHRU predicate which it uses to skip execution of the JOIN if the previous THEN condition was not met. I'm not sure why combined ANDs wouldn't use the same approach.

2. EXISTS OR EXISTS

The EXISTS OR EXISTS version used a concatenation (UNION ALL) operator as the inner input to an outer semi join. This arrangement means that it can stop requesting rows from the inner side as soon as the first one is returned (i.e. it can effectively short circuit) All 4 queries ended up with the same plan where the cheaper predicate was evaluated first.

/*All tests are testing "If True Or True"*/

IF EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=1)  
OR EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1)
PRINT 'Y'
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

IF EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1) 
OR EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)= 1) 
PRINT 'Y'
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

SELECT 1
WHERE  EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)= 1)  
OR EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1)
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

SELECT 1
WHERE  EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1) 
OR EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=1) 
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

3. Adding an ELSE

It did occur to me to try De Morgan's law to convert AND to OR and see if that made any difference. Converting the first query gives

IF NOT ((NOT EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
OR NOT EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)))
PRINT 'Y'
ELSE
PRINT 'N'
/*
Table 'spt_monitor'. Scan count 1, logical reads 1
Table 'spt_values'. Scan count 1, logical reads 9
*/

So this still doesn't make any difference to the short circuiting behaviour. However if you remove the NOT and reverse the order of the IF ... ELSE conditions it now does short circuit!

IF (NOT EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
OR NOT EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1))
PRINT 'N'
ELSE
PRINT 'Y'
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

Solution 2:

I believe you can rely on the short-circuiting behavior of IF statements in most, if not all, modern languages. You could try testing by putting a true condition first and replacing your second condition with 1/0 which would give you a divide by zero error if short circuiting doesn't occur, like so:

IF 1>0 OR 1/0 BEGIN
  PRINT 'Short Circuited'
END

If you don't trust that, you could always rewrite your query to do this:

IF EXISTS(SELECT...) BEGIN
  IF EXISTS(SELECT...) BEGIN
    ...
  END
END

Solution 3:

If I execute the query with an AND, even then , both the tables are accessed

SET STATISTICS IO ON IF EXISTS (SELECT * from master..spt_values where [name] = 'rpcc') and EXISTS(SELECT * from master..spt_monitor where pack_sent = 5235252) PRINT 'Y'

Table 'spt_monitor'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'spt_values'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Solution 4:

I'm taking the following quotes from the following blog entry on sqlteam:

How SQL Server short-circuits WHERE condition evaluation

It does when it feels like it, but not in the way you immediately think of.

As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.

For further details check the first link in the above blog entry, which is leading to another blog:

Does SQL Server Short-Circuit?

The final verdict? Well, I don't really have one yet, but it is probably safe to say that the only time you can ensure a specific short-circuit is when you express multiple WHEN conditions in a CASE expression. With standard boolean expressions, the optimizer will move things around as it sees fit based on the tables, indexes and data you are querying.

Solution 5:

Had an interesting observation. I have two tables tbla and tblb. tbla has a primary key (idvalue) which is used as a foreign key in tblb. Both has a row with idvalue = 1, but no row with idvalue of -1. Now, below query uses only one table

select 1
where exists
(select 1 from tbla where idvalue = -1)
and exists (select 1 from tblb where idvalue= 1)

Gives

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbla'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is obvious because the optimizer knows that since there is a primary key-foreign key relationship, so if the value is missing in tbla, it can never be present in tblb. So, optimizer will decide on runtime that seek on tblb is not required.

However, if I write the query as

select 1
where exists
(select 1 from tbla where idvalue = 1)
and exists (select 1 from tblb where idvalue= -1)

then both the tables are accessed. This is pretty obvious since here the optimizer knows that it has to check in both places to be sure that the AND condition is satisfied.

However, in both the cases, the actual execution plan shows seeks on both tbla and tblb. This seems strange to me. Any thoughts on this?