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.
midrange.com
