Re: SQL help with EXISTS

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.