Re: Question about joining tables
Date: Sun, 09 Mar 2008 18:34:28 -0300
Message-ID: <47d457e7$0$4071$9a566e8b_at_news.aliant.net>
JOG wrote:
> On Feb 23, 12:11 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
>>bissa..._at_yahoo.co.uk wrote: >> >>>Hi, >> >>>Say I have two tables, one called property which stores details of >>>properties, and anothe called property_image that stores a single >>>image for a property. So either property may have one image or no >>>image. I know how to do the following join: >> >>>SELECT * FROM property, property_image WHERE property.property_id = >>>property_image.property_id >> >>>Works fine if I need only returned the properties that tie up with >>>images but the properties that dont tie up I dont get returned in my >>>result. Is it possible to create an SQL joining statement where all >>>properties would be returned including the ones that dont have an >>>property image? Those that dont just wouldnt have any values in the >>>property_image.* columns. Traditionally I would do a query on the >>>property table then for each property do a single SELECT lookup on the >>>property_image table .. im sure for performance if i could reduce this >>>to one query i would see an improvement. >> >>>Thanks >> >>>Burnsy >> >>See UNION or LEFT OUTER JOIN. (Outer joins are just shorthands for >>unions but more people know outer joins than know unions for some reason.)
>
>
> I was just wondering about this, and its jarring. I'm struggling to
> even describe outer joins in terms of unions at all. F'rinstance, I
> was mulling over the example:
>
> R1 = { (a:1), (a:2) }
> R2 = { (a:1, c:1) }
> R1 OUTER JOIN R2 = { (a:1, c:1), (a:2, c:null) }
>
> Its the null 'value' that's the problem - where the hell has it
> appeared from?
When one writes the union, the NULL or whatever else one uses is a literal.
R1 JOIN R2
UNION
EXTEND (R1 MINUS R2{a}) ADD c=NULL
(a:2, b:null) wasn't a member of R1 so the union can't
> have simply been from that. If the union was with R1 extended with
> (c:null), well then the result of the outer join would also have (a:1,
> c:null) in it, which clearly isn't the case either. And if in a union
> we are automatically extending the unary tuples of R1 to be binary
> tuples to match the 'header' with the largest cardinality, well thats
> some change to the normal behaviour of set union. Outer joins...Ugh.
Null...ugh. Received on Sun Mar 09 2008 - 22:34:28 CET