ANY OR REPEATED EXISTING IN T-SQL

Question

I have a table that is a mapping between foreign identifiers and local identifiers.

I had to write a query to find out if this table is a bijection. I came up with this

IF 1 <> ANY( SELECT COUNT(foreignId) FROM mappingTable GROUP BY localId ) BEGIN SELECT 'Oh noes!' END ELSE BEGIN SELECT 'Everything is fine.' END 

My supervisor looked at it and grimaced, and told me that he should write this instead:

 IF EXISTS( SELECT NULL FROM mappingTable GROUP BY localId HAVING COUNT(foreignId) <> 1 ) BEGIN SELECT 'Oh noes!' END ELSE BEGIN SELECT 'Everything is fine.' END 

My question is which of these queries is the best style. I am sure they are equivalent.

Answer 1

Testing on SQL Server 2008 shows that these queries not only give the same results, but even have the same query plans. The query optimizer already knows that these queries are equivalent. Therefore, you are correct that any argument that promotes one another will have to focus on other aspects, such as style.

For me personally, the second query is easier to understand, although the first query follows in more detail how you will express your search in English, because I saw EXISTS much more than ANY . The first request makes me go “wait, what? Oh yes, that's right ...” The second is immediately obvious to me. This may be different for others (perhaps for you); you should try to make sure that your requests are just as easy to read for your supervisor and colleagues.

Answer 2

Your queries are equally bad or equally good depending on whether you have an index in the column or not.

Without an index, the table / cluster index scan of all rows will be deployed, followed by a Hash match that removes duplicates.

If you have an index in the column that you are checking, both queries will use that index and end earlier when a duplicate is found.

Query plan with index:

Query plan without index: