Why are my EXISTS vs. IN queries returning different results?

Question

Why are my EXISTS vs. IN queries returning different results?

T_Peters

Hall of Fame

Points: 3052
More actions
October 24, 2008 at 3:52 pm

#383231

Hi all,
My understanding is that EXISTS and IN are semantically equivalent, but I'm getting different result sets using them. The first query returns 2 rows (and is what I'm looking for) for the 2 tables that have the column types I'm looking for. The second query returns 4 rows for the 4 user tables in this database. Am I using the EXISTS clause wrong here?
SELECT *
FROM SYS.ALL_OBJECTS
WHERE NAME IN (
SELECT c.NAME
FROM SYS.ALL_COLUMNS a
JOIN SYS.TYPES b ON a.system_type_id = b.system_type_id
JOIN SYS.ALL_OBJECTS c ON a.object_id = c.object_id
WHERE b.NAME IN ('text', 'ntext', 'image', 'varchar(max)', 'nvarchar(max)', 'varbinary(max)', 'xml')
and c.TYPE = 'U')
AND TYPE = 'U'
SELECT *
FROM SYS.ALL_OBJECTS
WHERE EXISTS (
SELECT *
FROM SYS.ALL_COLUMNS a
JOIN SYS.TYPES b ON a.system_type_id = b.system_type_id
JOIN SYS.ALL_OBJECTS c ON a.object_id = c.object_id
WHERE b.NAME IN ('text', 'ntext', 'image', 'varchar(max)', 'nvarchar(max)', 'varbinary(max)', 'xml')
and c.TYPE = 'U')
AND TYPE = 'U'
Thanks,
Tim

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Exists just checks whether or not the resultset returns a row. In your exists, there is no link between the subquery and the outer query. Hence the exists will return the same for all rows, true in this case, as that subquery will always have at least one row in it.

Look at it this way. With the IN, there's a comparison between the value returned by the subquery and the value that the IN is applied to. There has to be a similar comparison with the exists.

Try this:

SELECT *

FROM SYS.ALL_OBJECTS c

WHERE EXISTS (

SELECT *

FROM SYS.ALL_COLUMNS a

JOIN SYS.TYPES b ON a.system_type_id = b.system_type_id

WHERE b.NAME IN ('text', 'ntext', 'image', 'varchar(max)', 'nvarchar(max)', 'varbinary(max)', 'xml')

AND a.object_id = c.object_id -- link to outer query

)

AND TYPE = 'U'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Oh, and also, the name in sys.types will never return varchar(max), nvarchar(max) or varbinary(max). They'll return varchar, nvarchar or varbinary. To see if it's max or a defined length, check the max_length column. -1 means max

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

T_Peters Hall of Fame Points: 3052 More actions · Answer 3

Thanks!!

The example in BOL shows how they can be used interchangeably, but leaves out the key portion you showed me.

Thanks,

Tim

T_Peters Hall of Fame Points: 3052 More actions · Answer 4

GilaMonster (10/24/2008)
Oh, and also, the name in sys.types will never return varchar(max), nvarchar(max) or varbinary(max). They'll return varchar, nvarchar or varbinary. To see if it's max or a defined length, check the max_length column. -1 means max

Thanks again, I've got that added now too 😀