Svein Erling wrote:

>>>>This query works fine on by DB:
>>>>
>>>>select pensionPlan_ID from DB union
>>>>select pensionPlan_ID from DC
>>>>
>>>>but this one does not:
>>>>
>>>>select * from PensionPlans where PensionPland_Id in
>>>>(select pensionPlan_ID from DB union
>>>>select pensionPlan_ID from DC)
>>>>
>>>>It does not like the word 'union'
>>>>
>>>>
>>>Nope. Subqueries must return scalar output (one row, one column)
>>>and a union is by nature 2 or more rows ---> so verboten.
>>>
>>>
>>One column, yes. But surely not one row. It depends on the context.
>>In this context, what's to stop me doing :
>>
>>Without a UNION, this sort of thing works fine, I do it all the
>>time.
>>
>>
>
>Jonathan, have you ever tried writing
>
>select * from PensionPlans where PensionPland_Id in (5 union 7)
>
>

No, this surely wouldn't work.

>I do not think it will work, because I have never heard of that
>syntax. There are at least two ways of writing a statement doing what
>you want, but it cannot be written using your syntax. The closest to
>your syntax would be
>
>select * from PensionPlans where
>PensionPland_Id in (select pensionPlan_ID from DB) or
>PensionPland_Id in (select pensionPlan_ID from DC)
>
>

I wrote :

select * from PensionPlans where PensionPland_Id in (select pensionPlan_ID from DB);

but of course, in order to get the desired results, one would have to
use the syntax you mentionned above. I was simply pointing out that
subqueries are _not_ always required to return only one row.

>The way I prefer to write this kind of statements is
>
>select * from PensionPlans PP where
>EXISTS(select * from DB where PP.PensionPland_Id=DB.pensionPlan_ID) or
>EXISTS(select * from DC where PP.PensionPland_Id=DC.pensionPlan_ID)
>
>The reason I prefer this, is simply that I became accustomed to it
>some time ago when using IN (<subselect>) on huge datasets was
>extremely time consuming.
>

Yes, I suppose your syntax is better for that reason. Since the
subselect is only getting one record at a time, it should go faster.

Jonathan Neve.



[Non-text portions of this message have been removed]