CODEWITHSUNDEEP
sqlsql-servert-sql
guisantogui
guisantogui

Reputation: 4136

Empty IN sql statement

if I have this sql:

SELECT A FROM B WHERE C IN 
(
   SELECT D FROM E
)

If my internal select don't return any result (0 rows) my where statement will be true or false?

I'm using SQL Server

Upvotes: 1

Views: 239

Answers (3)

Anon
Anon

Reputation: 10918

Ignore the tables for the moment and think about what this does:

SELECT 'Yes' WHERE 1 IN (SELECT 1 WHERE 1=0)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

This is your query:

SELECT A
FROM B
WHERE C IN (SELECT D FROM E);

The where statement is quite simple. It filters out rows there is no match between B.C and E.D. By your statement, there is no match, so all rows are filtered out. The query returns no rows.

The where statement is not "true" or "false" in general. It is "true" or "false" for a given row in B (in this case). With no matches, the where clause will be uniformly false for all rows.

Upvotes: 4

BlackICE
BlackICE

Reputation: 8926

Neither, if the inner query returns no rows, you won't get any results returned. Selecting anything from an empty set will give you an empty set. IMO an empty set is neither true nor false in and of itself, only when compared to something else. So if the question is "will I get any results", then the answer is false.

Upvotes: 0

Related Questions