SQL Server Conditional Flow

Asked 4/4, 2011 at 18:54 Answered 14/4, 2011 at 8:49

sql sql-server t-sql if-statement short-circuiting

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

March answered 4/4, 2011 at 18:54 Comment(1)

sorry, instead of "AND" I typed "OR" – March 4/4, 2011 at 19:0

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
*/

Burne answered 4/4, 2011 at 20:32 Comment(4)

CASE isn't guaranteed to short circuit... bartduncansql.wordpress.com/2011/03/03/… which shatters a few illusions, eh? – Clavicytherium 13/4, 2011 at 19:46

@Clavicytherium - It is guaranteed to short circuit at run time though! This is a constant folding issue and SQL Server won't even generate a plan. AFAIK if you have an execution plan there is no example of it not short circuiting. – Burne 13/4, 2011 at 19:48

ah, acknowledged bug too. Missed the update. And found this too #789731 – Clavicytherium 13/4, 2011 at 19:50

@Clavicytherium - But see this – Burne 19/9, 2011 at 18:16

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

Granulose answered 4/4, 2011 at 19:4 Comment(1)

You cannot/ generally rely on this in SQL Server. As a rule of thumb the optimiser can rearrange most things. I'd use the second option to be sure that the behaviour I wanted happened. Discussed here https://mcmap.net/q/672014/-sql-server-predicates-lazy – Burne 4/4, 2011 at 19:59

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.

Effie answered 5/4, 2011 at 3:39 Comment(2)

I ran into the same problem. - Krish – March 5/4, 2011 at 14:23

+1 Well you've demonstrated that you definitely cannot rely on it shortcircuiting then! Interesting to compare the execution plan with the one for this.

IF CASE WHEN EXISTS (SELECT * from master..spt_values where [name] = 'rpcc') THEN CASE WHEN EXISTS (SELECT * from master..spt_monitor where pack_sent = 5235252) THEN 1 END END = 1 PRINT 'Y'

They are nearly identical but the CASE one uses a PASSTHRU predicate and doesn't touch the second table. – Burne 5/4, 2011 at 21:3

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.

Felder answered 12/4, 2011 at 16:29 Comment(0)

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?

Effie answered 5/4, 2011 at 4:54 Comment(1)

I don't think the FK makes any difference here. The difference seems to be that in a WHERE clause it does short circuit this query. I get different stats for both the following Version1: if exists (select 1 from tbla where idvalue = -1) and exists (select 1 from tblb where idvalue= 1) print 'Y' and Version2: select 1 where exists (select 1 from tbla where idvalue = -1) and exists (select 1 from tblb where idvalue= 1) – Burne 5/4, 2011 at 23:43

Nope.

I just tested in SQL Server 2008 and if the first evaluation fails it immediately skips the IF block.

This is very easy to test.

For your first evaluation do something like IF 1=0 and for your second do anything, then show actual exec plan. In mine it only does a Constant Scan to eval those constants.

Cracked answered 4/4, 2011 at 19:3 Comment(8)

It works exactly as you said in SQL Server if I use variables, but I dont think it works if you use EXISTS (SELECT...). I found that SQL Server executes both queries. I dont know how to stop SQL Server to stop executing both without breaking my statements into multiple IF. – March 4/4, 2011 at 19:28

@March - How are you verifying this? If I do SET STATISTICS IO ON IF EXISTS (SELECT * from master..spt_values) OR EXISTS(SELECT * from master..spt_monitor) PRINT 'Y' then I see only 1 table was accessed despite both appearing in the plan. – Burne 4/4, 2011 at 20:7

Hi Martin, Thanks for your response. As soon as I add where condition to your query, query plan is different. SET STATISTICS IO ON IF EXISTS (SELECT * from master..spt_values where [name] = 'rpcc') or EXISTS(SELECT * from master..spt_monitor where pack_sent = 5235252) PRINT 'Y' – March 4/4, 2011 at 21:46

@March - But both of those conditions are false. So it needs to check both whichever order it does it in to see if the other one is true. – Burne 4/4, 2011 at 21:48

Martin, SET STATISTICS IO ON IF EXISTS (SELECT * from master..spt_values where [name] = 'rpcc') and EXISTS(SELECT * from master..spt_monitor where pack_sent = 523525233333) PRINT 'Y', If I run this query, both runs even the both query returns false. – March 5/4, 2011 at 16:59

@Cracked - Your example just illustrates contradiction detection not short circuiting. – Burne 5/4, 2011 at 22:54

IF EXISTS(subquery1) AND EXISTS(subquery2) never appears to short circuit in fact... – Burne 7/4, 2011 at 12:41

@Martin - I see that! How bizarre. – Cracked 7/4, 2011 at 12:45

You can prevent the second scan by doing this:

declare @test bit
select @test = case when exists(select 1...) then 1 else 0 end
if @test = 1
begin
    --1st test passed
    select @test = case when exists(select 2...) then 1 else 0 end
end
if @test = 1
begin
    print 'both exists passed'
end

Gunderson answered 14/4, 2011 at 8:49 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

1. EXISTS AND EXISTS

2. EXISTS OR EXISTS

3. Adding an ELSE

Recommended topics

Hot tags

1. `EXISTS AND EXISTS`

2. `EXISTS OR EXISTS`

3. Adding an `ELSE`