Re: Urgent help
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
For one thing, how would you place brackets in this example?
where a.x=b.x and b.y=c.y and b.z=d.z
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