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)