Re: Question about joining tables

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? (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. Received on Sun Mar 09 2008 - 15:21:55 CET