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.


ANY and SOME - Lesser Known T-SQL Keywords
Published: Dec 01, 2020
This was something else that came up in training and I can’t, for the life of me, remember how and why, but we seemed to end up on the topic and this came out. I thought I’d write a little blog about them just because I was amused (doesn’t take much) by how few people knew these even existed in the T-SQL language.

So what are they? Well, they’re basically the same as EXISTS, just a different syntax:

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

select *
from Production.product p
where p.ProductID = any
(
      
select productID
      
from Production.TransactionHistoryArchive t
)



As you can see… they’re all identical execution plans. This is because internally the SQL Optimizer interprets them all as a “SOME” operator. I’ll prove that in my next blog post but, for the moment, you’ll just have to trust me on that one.

As mentioned, the only differences in these keywords are the syntax usage when you write the code. You can work them all out for yourself, but I’ll start you off with the following:

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

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


The above are identical and therefore, personally, I think that the SOME / ANY syntax is actually cleaner for a simple example… although if you want to add more complex join criteria then you’ll likely be best off writing it as an EXISTS.

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.