Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Where Exists - Quick Tip
Published: Dec 03, 2020
Yet another question from one of my training courses (a very useful and fun way to obtain blog topics) was surrounding the difference between the following and whether one or the other should be being used:

select *
from Production.product p
where exists
(
      
select *
      
from Production.TransactionHistoryArchive t
      
where p.ProductID <= t.ProductID
)

select *
from Production.product p
where exists
(
      
select 1
      
from Production.TransactionHistoryArchive t
      
where p.ProductID <= t.ProductID
)


Well, let’s start with the quick answer… “no – there’s no difference”:


So why would this warrant a blog post? It was only because of a small internal quirk I wanted to share which comes as a result of the trace flags from my last post:

select *
from Production.product p
where exists
(
      
select *
      
from Production.TransactionHistoryArchive t
      
where p.ProductID = t.ProductID
)
option (querytraceon 3604, querytraceon 8606, querytraceon 8621)



As you can see in the above output from our Trace Flags (highlighted), the Optimizer knows what an EXISTS is trying to do and therefore actually swaps the column list for “Value=1” instead. Therefore both * and 1 are completely correct in the original queries above… and both are simply translated to 1 internally anyway… so it doesn’t matter which you use… although you COULD argue that writing 1 in the first place saves the Optimizer from replacing * with 1… pretty sure that would be the most pedantic argument in the world though… :o)

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.