Re: Urgent help

From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Mon, 28 Jul 2003 09:48:52 -0700
Message-ID: <wBcVa.14$HN6.66_at_news.oracle.com>


"Lennart Jonsson" <lennart_at_kommunicera.umea.se> wrote in message news:6dae7e65.0307280207.62e12f8a_at_posting.google.com...
> SELECT BTEmpty.BucketID,
> BTEmpty.Time AS Empty,
> BTHalf.Time AS Half,
> BTFull.Time AS "Full"
> FROM Bucket AS BTEmpty
> INNER JOIN
> Bucket AS BTHalf
> ON BTEmpty.BucketID = BTHalf.BucketID AND
> BTEmpty.WaterLevel = 'Empty' AND
> BTHalf.WaterLevel = 'Half'
> INNER JOIN
> Bucket AS BTFull
> ON BTFull.BucketID = BTEmpty.BucketID AND
> BTFull.WaterLevel = 'Full'

I have a trouble understanding ANSI "inner join" syntax. In the example above, shouldn't it be:

 FROM (Bucket AS BTEmpty

             INNER JOIN
             Bucket AS BTHalf
             ON BTEmpty.BucketID = BTHalf.BucketID AND
                    BTEmpty.WaterLevel = 'Empty' AND
                    BTHalf.WaterLevel = 'Half') firstJoin
             INNER JOIN
             Bucket AS BTFull
             ON BTFull.BucketID = firstJoin.BucketID AND
                   BTFull.WaterLevel = 'Full'

Join is a binary operation, after all. I believe natural join is binary operation in the relational algebra as well.

Given a binary operation, one can omit brakets if he proves that the operation is associative. However, it is easy to see that join is not associative:

select * from A,B,C,D
where a.x=b.x and b.y=c.y and b.z=d.z

For one thing, how would you place brackets in this example?

Now, unlike join, cartesian product is associative, so that we can simply write comma separated list of relations and add some join predicates into the where clause.

Not to mention that Lennard put single table predicates in the "on" clause. Aren't those supposed to go into the "where" clause? Received on Mon Jul 28 2003 - 18:48:52 CEST

Original text of this message