Re: Question about joining tables

From: Bob Badour <>
Date: Sun, 09 Mar 2008 18:34:28 -0300
Message-ID: <47d457e7$0$4071$>

JOG wrote:
> On Feb 23, 12:11 am, Bob Badour <> wrote:

>> wrote:
>>>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 =
>>>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.
>>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.


  (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

Original text of this message