exists and not exists

Tools & Utilities

exists and not exists

there is a query
sel * from a where not exists(sel 1 from b join a on a.a1=b.b1);

as far as i know
With a correlated subquery, for EACH row in the main query a search of the subquery is made to find
a matching matching row, then a T/F value is returned.

but sel 1 will return only sinlgle col with all rows as 1
so i am confused how exactly EACH row in the main query is made to find
a matching matching row.

Also please tell me when it is suitable to use EXISTS/NOT EXISTS in place of OUTER JOINS?

Re: exists and not exists


If i am right..NOT EXISTS works like this...

The subquery is executed first...in your case..1 will be returned from the subquery if at all there exists a row that satisfies the condition..none of the rows are returned as the final resultset.If there is no row satisfying the condition..the NOT EXISTS condition becomes sucessful..All the rows from the table a are retrieved as the final result set..The processing is not done for each row...

Hope this helps...