CODEWITHSUNDEEP
sqlsql-server
Aditya
Aditya

Reputation: 2311

How does the EXISTS Clause work in SQL Server?

I tried with below queries and bit confused about working of EXISTS clause, although I know it evaluates for TRUE/FALSE.

Below #1 SQL sub-query anyhow returns 0, but still the result of the combined query is 1.

1. SELECT 1 WHERE EXISTS ( SELECT 0 WHERE 1 = 1 )

2. SELECT 1 WHERE EXISTS ( SELECT 0 WHERE 1 = 0 )

Also, if EXISTS clause evaluates for TRUE/FALSE, then why does the below one not work?

SELECT 1 WHERE EXISTS ( 1 )

Please help me understand this situation.

EDIT: EXISTS clause evaluates for TRUE/FALSE. The condition is TRUE if a sub-query returns any result. Then why EXISTS ( 1 ) does not work ? It is a TRUE condition.

Upvotes: 4

Views: 1370

Answers (3)

Vahid Heydarinezhad
Vahid Heydarinezhad

Reputation: 126

Behavior:

Condition          Is true if ...
----------         ------------------
EXISTS             the subquery returns at least one row
NOT EXISTS         the subquery returns no data

Usage:

WHERE EXISTS ( subquery );
WHERE NOT EXISTS  ( subquery );

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

From documentation EXISTS:

Specifies a subquery to test for the existence of rows.

SELECT 1 
WHERE EXISTS ( SELECT 0 WHERE 1 = 1 )
-- there is row

SELECT 1 
WHERE EXISTS ( SELECT 0 WHERE 1 = 0 )
-- no row returned by subquery

SELECT 1 WHERE EXISTS ( 1 )
-- not even valid query `1` is not subquery

Keep in mind that it checks rows not values so:

SELECT 1 
WHERE EXISTS ( SELECT NULL WHERE 1 = 1 )
-- will return 1

LiveDemo

EDIT:

This seems contradictory with the sentence " EXISTS clause evaluates for TRUE/FALSE" ?

EXISTS operator tests for the existence of rows and it returns TRUE/FALSE.

So if subquery returns:

╔══════════╗     ╔══════════╗     ╔══════════╗     ╔══════════╗
║ subquery ║     ║ subquery ║     ║ subquery ║     ║ subquery ║
╠══════════╣     ╠══════════╣     ╠══════════╣     ╠══════════╣
║ NULL     ║     ║ 1        ║     ║ 0        ║     ║anything  ║
╚══════════╝     ╚══════════╝     ╚══════════╝     ╚══════════╝

Then EXISTS (subquery) -> TRUE.

If subquery returns (no rows):

╔══════════╗
║ subquery ║
╚══════════╝

Then EXISTS (subquery) -> FALSE.

Upvotes: 3

Derek Elkins left SE
Derek Elkins left SE

Reputation: 2079

EXISTS returns true when the subquery within it has any rows. A logically equivalent (but not recommended) way of rewriting an EXISTS expression is:

SELECT 1
WHERE (SELECT COUNT(*) FROM (SELECT 0 WHERE 1 = 1)) > 0

In this rewriting, your last query looks like:

SELECT 1
WHERE (SELECT COUNT(*) FROM 1) > 0

which you should see doesn't make sense.

Upvotes: 1

Related Questions