MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2012

Re: SQL help with EXISTS



fixed

On 06 Dec 2012 11:52, Stone, Joel wrote:
So - you are stating that the SQL I presented CANNOT function?

No. There is nothing about the given SQL statement that precludes functionality. If the fullselect can complete without errors, then that is effectively all that is required for the functionality of the overall query.

But it does. It returns a set based on the EXISTS

Yep. That is expected. Rewritten more generically, the following is a representation of the given SQL:

select ...
from ...
where (parenthetical-predicate)
and EXISTS (fullselect)

Effectively that query first selects every row that is selected according to the first parenthetical predicate in the WHERE clause, but then omits that row *if* that row does not also satisfy [per AND logic] the EXISTS predicate.

I don't understand.

It is the effect of the fullselect in the query that is relevant, and that is apparently what is not understood :-(

The fullselect generates either an empty set or a non-empty set. When the result of the fullselect is the empty set, then the EXISTS predicate is false. When the result of the fullselect is a not-empty set [i.e. at least one row will be selected], then the EXISTS predicate is true. Thus restated... When the logic of the fullselect yields zero rows, then the EXISTS predicate is false. When the logic of the fullselect yields *any* row, then the EXISTS predicate is true.

Whatever are the columns and expressions [even constant\literal as expression] that are selected by the fullselect is moot, with regards to whether zero versus any rows are selected. Whatever /values/ are derived [from columns, expressions, or constants] from any row that is selected by the fullselect is moot, as only the fact that at least one row was selected, is relevant.






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact