On 12/3/2012 8:26 PM, YAN HONG YE wrote:
SELECT c FROM mytable AS m1 WHERE NOT EXISTS (SELECT b FROM mytable AS m2
WHERE m2.b = m1.c);
SELECT c FROM mytable WHERE NOT EXISTS (SELECT b FROM mytable WHERE b = c);
The second query is equivalent to this:
SELECT c FROM mytable AS m1 WHERE NOT EXISTS (SELECT b FROM mytable AS
m2 WHERE m2.b = m2.c);
Note the very last column reference is to m2.c, not m1.c. In other
words, the subquery doesn't depend on the current row in the outer query
at all. Either there are rows in mytable where b==c (that is, two equal
values in the same row), or there aren't. If there are, then NOT EXISTS
check is always false, and the overall query would return zero rows. If
there aren't, then NOT EXISTS check is always true, and the overall
query would return all rows in mytable.
On the other hand, the first query uses a so-called correlated subquery:
the results of the subquery depend on the current row in the outer query
being processed.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
