A question about the EXISTS operator

Question

Post reply

A question about the EXISTS operator

ReamerXXVI

Ten Centuries

Points: 1379
More actions
March 25, 2019 at 2:23 am

#377954

In the ye olde AdventureWorks2016 database if I run the following query it returns Exists, even though the sub-query returns no rows, I'm trying to under why.
SELECT CASE WHEN EXISTS
(
    SELECT COUNT(*)
    FROM Production.Product
    WHERE ProductID = 9999
) THEN 'Exists' ELSE 'Does not Exist' END
I have tried a few things including checking the @@Rowcount before and after the query e.g.
SELECT @@Rowcount -- Returns 0
SELECT COUNT(*)
FROM Production.Product
WHERE ProductID = 9999 -- Returns 0
SELECT @@Rowcount -- Returns 1
Is the reason it returns Exists when using the case statement because it returned a row count of 1 to the outer query?
Any help appreciated.

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

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

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 1

It will exist with a count of zero.
You need to replace 'COUNT(*)' with '*'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

According to BOL, EXISTS "Returns TRUE if a subquery contains any rows."

Your subquery will always return one row, where the integer column value will be 0 or greater:
SELECT COUNT(*)
FROM Production.Product
WHERE ProductID = 9999

Note that COUNT will count all qualifying rows. The EXISTS operator will stop when a qualifying row is first found, regardless of how many there are. Change the syntax of your query only slightly to benefit from this optimization, and to return zero rows if no qualifying rows exist:

EXISTS(
SELECT 1 FROM Production.Product
WHERE ProductID = 9999
)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

drew.allen SSC Guru Points: 76995 More actions · Answer 3

ReamerXXVI - Monday, March 25, 2019 2:23 AM

In the ye olde

'ye' in this context is a misunderstanding of 'Ã°e', where 'Ã°' is the old spelling of 'th' as in 'the'. In fact, 'Ã°e' IS the old spelling of 'the', so you are essentially saying 'in the the olde....'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

drew.allen - Monday, March 25, 2019 10:52 AM

ReamerXXVI - Monday, March 25, 2019 2:23 AM
In the ye olde

'ye' in this context is a misunderstanding of 'Ã°e', where 'Ã°' is the old spelling of 'th' as in 'the'. In fact, 'Ã°e' IS the old spelling of 'the', so you are essentially saying 'in the the olde....'

Drew

Playing on the fringe of various medieval societies in England, I've come across this statement from time to time. Makes good sense to me.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101115 More actions · Answer 5

Yes. You can say either:

In the old AdventureWorks2016 database
In ye olde AdventureWorks2016 database

but I don't think they should be mixed 😉

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".