CODEWITHSUNDEEP
sql
Bruce Jinru Su
Bruce Jinru Su

Reputation: 189

The use of NOT EXISTS in SQL

If table A has an int num and table B has an int q

Do the following two queries always give the same result?

Q1:

select num
from A
where num>any(select q from B)

Q2:

select num
from A
where not exists(select * from B where num<=q);

Thank you

Upvotes: 1

Views: 87

Answers (1)

ruakh
ruakh

Reputation: 183564

No, they're quite different. The first one looks for A records where A.num is greater than some B.q; that is, if there's any B.q that's less than a given A.num, then that A.num will appear in the result. The second one looks for A records where A.num is greater than all B.qs; that is, any B.q that's not less than a given A.num, then that A.num will not appear in the result.

Assuming that the second query is approximately what you wanted, then you should change ANY to ALL in the first query.

Even then, however, there's a difference, in that they handle nulls differently:

  • If B.q is ever null, then the version with ALL will never return any rows (because the null is taken to mean "unknown", so potentially arbitrary large, and the WHERE clause only accepts rows that are known to meet the condition), whereas the version with NOT EXISTS will simply ignore those nulls (since they're filtered out by the inner WHERE clause).
  • If A.num is ever null, then the version with ALL will omit those nulls (since they're filtered out by the WHERE clause), whereas the version with NOT EXISTS will include them (since they're filtered out by the inner WHERE clause).

(Edited to add: As dav1dsm1th implies in a comment above, this assumes that B does not have any column named num. If B does have a column named num, then the version with NOT EXISTS is actually referring to B.num rather than A.num, and its behavior is again completely different from the version with ALL. This can be fixed by explicitly writing A.num rather than simply num in the subquery.)

Upvotes: 2

Related Questions