Question about joining tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bissatch@yahoo.co.uk - 22 Feb 2008 23:26 GMT

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

Reply to this Message

Bob Badour - 23 Feb 2008 00:11 GMT

> Hi,
>
[quoted text clipped - 19 lines]
>
> 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.)

Reply to this Message

JOG - 09 Mar 2008 15:21 GMT

> bissa...@yahoo.co.uk wrote:
> > Hi,
[quoted text clipped - 23 lines]
> 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.

Reply to this Message

Bob Badour - 09 Mar 2008 22:34 GMT

>>bissa...@yahoo.co.uk wrote:
>>
[quoted text clipped - 35 lines]
> 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.

Reply to this Message

Knowledgy - 28 Feb 2008 23:50 GMT

SELECT *
FROM property p LEFT OUTER JOIN property_image pi
ON p.property_id = pi.property_id

Use the newer ansi join syntax. DBMS systems are deprecating
the older style join you're using

Signature

Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/

Atlanta's Business Intelligence and Data Warehouse Experts

> Hi,
>
[quoted text clipped - 19 lines]
>
> Burnsy

Reply to this Message

Roy Hann - 29 Feb 2008 10:25 GMT

> SELECT *
> FROM property p LEFT OUTER JOIN property_image pi
> ON p.property_id = pi.property_id
>
> Use the newer ansi join syntax. DBMS systems are deprecating
> the older style join you're using

Deprecating it? Really? I'd not heard that, and I find it hard to believe
now I have.

Roy

Reply to this Message

Jan Hidders - 29 Feb 2008 12:44 GMT

> > SELECT *
> > FROM property p LEFT OUTER JOIN property_image pi
[quoted text clipped - 5 lines]
> Deprecating it? Really? I'd not heard that, and I find it hard to believe
> now I have.

Neither can I. So I'm very curious (a) what that exactly means and (b)
what evidence there is for this. So John, could you shed some light on
this?

-- Jan Hidders

Reply to this Message

-CELKO- - 10 Mar 2008 16:08 GMT

>> Deprecating it? Really? I'd not heard that, and I find it hard to believe now I have. <<

Microsoft SQL Server has dropped their old *= in the 2008 version. A
lot of the newer SQLs never had a proprietary syntax, so they were
created with the infixed syntax from the start. I am not sure what
the official word from Oracle (who is usually very slow to deprecate)
or DB2.

Reply to this Message