EXISTS() vs. SELECT COUNT(*) vs. EXISTS (SELECT *)

I was reading about optimizing EXISTS today and decided to try a couple of variations on this theme. In this example, all I want to know is does the ID exist in the table. I presumed the following would all work exactly the same:

Select count(*) From Jurisdiction WITH (NOLOCK) Where JID= 22

IF EXISTS(Select count(*) From Jurisdiction WITH (NOLOCK) Where JID= 22)
print 'exists 1'

IF EXISTS(Select * From Jurisdiction WITH (NOLOCK) Where JID= 22)
print 'exists 2'

IF EXISTS(Select 1 From Jurisdiction WITH (NOLOCK) Where JID= 22)
print 'exists 3'

To test, I turned on 'Include Actual Execution Plan' and ran.
To my surprise, the first, third and fourth queries each took 33% of the total but the second took zero. They all return the correct result - the first query returns '1', the rest print 'exists x'.
In the execution plan for the first, third and fourth queries, 100% of the time taken for each query is attributed to a clustered index seek. The second query only consists of a Constant scan.
Can you explain this? Is it using something in memory that it's not showing?

I have tried this using a different table in a different database on a different server just for giggles. This table has no indexes. Exactly the same result except I get a Table scans instead of clustered index seeks.

If nobody can explain what I'm seeing here, can someone try it and let me know if they see the same result.

I think

IF EXISTS(Select count(*) From Jurisdiction WITH (NOLOCK) Where JID= 22)

does not need to query anything from the table because even if count(*) is 0 it always returns a resultset, exists returns true. That is true for any kind of grouping operator like max, min, sum.

So it is always true.

Running these on master database

Select * From sysobjects WITH (NOLOCK) Where ID= 22
Select count(*) From sysobjects WITH (NOLOCK) Where ID= 22


name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
-------------------------------------------------------------------------------------------------------------------------------- ----------- ----- ------ ------ ----------- --------------- ----------- ----------- ----------------------- ------- ----------- ---------------- ---- -------- ------- -------- ----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ------

(0 row(s) affected)


-----------
0

(1 row(s) affected)

Second query returned a row with 0 as count, thus exists is always true, it is not looking for count(*) value but rows returned value.

In other words, the second query is not logically same as other queries.

Thanks for that. Obvious really, wasn't it. (Not that I thought of it!)