Re: SQL help with EXISTS

On 06 Dec 2012 11:06, Stone, Joel wrote:

I don't think that is correct. It seems to do the subselect (return
a set of rows) and THEN join the result to the outer select. A few
responses have stated what you said - that EXISTS simply returns TRUE
or FALSE. But I think that it must do this for each row in the outer
select in the case where a join is occurring?

Maybe I am wrong, I don't know SQL and it is elusive and powerful and
complex so I don't understand all the subtleties.

Dan Kimmel on Thursday, December 06, 2012 11:01 AM wrote:

The EXISTS says "are there any". You get the same results in the
outer select whether there's one or many results from the
subselect.

The documentation states explicitly that the "values returned by the fullselect [of the EXISTS predicate] are ignored" and states clearly that the only possible results are True and False. No type of matching join can be done without some values against which to compare; that would leave only a cartesian product join, and that certainly would be of no value to the query.

IBM i 7.1 Information Center -> Database -> Reference -> SQL reference -> Language elements -> Predicates
_i EXISTS predicate i_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzexists.htm
"The EXISTS predicate tests for the existence of certain rows.

>>-EXISTS--( fullselect )---------------------------------><

The fullselect may specify any number of columns, and

* The result is true only if the number of rows specified by the fullselect is not zero.
* The result is false only if the number of rows specified by the fullselect is zero.
* The result cannot be unknown.

The values returned by the fullselect are ignored.
..."