What's the difference between 'ANY' and 'EXISTS' in sql-server

user_6031830 0
Viewed 2511

When I'm running

select code from account where exists (select account from store)
except 
select code from account where code = any (select account from store)

it brings me 2 results which means that they are not the same. From my research, I haven't found any difference between them. 'Any' is rarely used from what I can find. Can anyone help me on this?

sql-server sql
1 Answers

The two queries are quite different.

The first query returns all rows or no rows depending on whether the subquery returns any rows at all or no rows.

You intend a correlated subquery:

select code from account where exists (select 1 from store where store.account = account.code)

These should be equivalent.

user_11440350
Related