Re: Two ways to quote your query ?1

Brian,

Your story tells that matters when the business filter is evaluate (before or after the joins).

I should admit that I was inconsequent when I presented the queries. Both should have the same type of join (i.e, LEFT, RIGHT or FULL). My question is why the result sets are the same in case of RIGHT (this is a little bit understandable) and why are not the same in case of FULL ?.

Brian Kelly wrote in message ...
>Different 2 vs 8 rows
>--
>Brian Kelly
>MCT, MCSE+I, MCDBA
>New Horizons - Boston
>Dan G <Gavrilescu.Daniel_at_pmintl.ch> wrote in message
>news:7sd92o$abk$1_at_pollux.ip-plus.net...
>> Try this. Is the result the same or not and why ?
>>
>> SELECT DISTINCT s.pub_id
>> FROM publishers S
>> FULL JOIN titles sp
>> ON s.pub_id=sp.pub_id
>> WHERE sp.type='business '
>First example performs a FULL JOIN which results in a list of all 8
>publishers, THEN applies the criteria of "Business" to eliminate all but
the
>two who have Bus titles
>
>> SELECT DISTINCT s.pub_id
>> FROM publishers S
>> LEFT JOIN
>> (SELECT sp.pub_id
>> FROM titles sp
>> WHERE sp.type='business ') as SP
>> ON s.pub_id=sp.pub_id
>
>This example performs a Left Join of Publishers against SP (which is a
>derived table consisting of the two publishers who have Bus titles) The
>criteria only affected the inner table(SP). Now the LEFT JOIN brings in all
>of the Publisher IDs including those that do not have a match in SP.
>HTH
>
>
Received on Mon Sep 27 1999 - 02:58:49 CDT