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
3 Answers
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.
3 Comments
zerkms
"The
where statement is quite simple. It returns rows when there is a match between " --- may be poorly worded, but from this phrase one might think that "where statement" returns rows by itself.
Gordon Linoff
@zerkms . . . I know what you are saying (and I changed the wording). The problem is that I can't think of a good word to mean the opposite of "filter out". "A
where clause filters in rows that match the conditions" isn't quite right.zerkms
I personally think that "A
WHERE clause filters rows" is a perfect wording.Ignore the tables for the moment and think about what this does:
SELECT 'Yes' WHERE 1 IN (SELECT 1 WHERE 1=0)
Comments
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.
10 Comments
zerkms
"my where statement will be true or false" --- obviously it will be
false
Teja
This can be a comment rather than answer.
JNK
@zerkms
WHEREs aren't true or false, but determine WHICH rows match the criteria.JNK
@zerkms correct, for a given RECORD. It's not accurate to say the
Where clause will be false unless you use something like WHERE 1 = 0. No rows will match the criteria.JNK
@zerkms no we can't because these are tables. No rows match the current data but tomorrow who knows.
|
C IN (...)expression, which is eithertrueorfalsefor the whole result set.INoperator doesn't returnnot foundit returns eitherfalseortrue