# 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'

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