Re: Two ways to quote your query ?1

From: Brian Kelly <bkelly_at_mediaone.net>
Date: Thu, 23 Sep 1999 10:56:33 -0400
Message-ID: <eBpLpLdB$GA.226@cppssbbsa03>

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 Thu Sep 23 1999 - 09:56:33 CDT