RE: [HACKERS] Enhancing PGSQL to be compatible with Informix SQL

>> > Yes, OUTER is an Informix-ism. Oracle uses *=. I think the first is
>> > easier to add and makes more sense for us. *= could be defined by
>> > someone as an operator, and overloading our already complex operator
>> > code to do *= for OUTER may be too complex for people to understand.
>> >
>> > It would be:
>> >
>> > SELECT *
>> > FROM tab1, OUTER tab2
>> > WHERE tab1.col1 = tab2.col2
>>
>> What about >2 table joins? Wish I had my book here, but I though tyou
>> could do multiple OUTER joins, no?

Oracle uses a syntax which I quite like. The query above would become:

SELECT *
FROM tab, tab2
WHERE tab1.col1 = tab2.col2 (+)

I've actually used queries something like this:

SELECT blah, blah, blah
FROM t1, t2, t3, t4
WHERE t1.start_date BETWEEN t2.start_date (+) AND t2.end_date (+)
AND t1.y = t2.y (+)
AND t3.x (+) = t1.x
AND t3.y (+) = t1.y
AND t4.x = t1.x;

For example...

I realise that this is not standard, but it's easy to read, and easy to
develop.

The problem with OUTER is: OUTER on which relationship? Does this matter?
I haven't thought about it hugely, but it may not make sense when you try to
do this:

SELECT *
FROM t1, OUTER t2, t3
WHERE t1.x = t2.x
AND t2.y = t3.y

Which is the OUTER join? Outer joining to t1 and inner joining to t3 gives
(I think) a different result to inner joining to t1 and outer joining to t3.
Then you have to start creating language rules to help determine which join
becomes the outer join, and it becomes a bit of a mess. With Oracle's
notation, it's pretty clear (I think anyway).

Hope this adds some fuel to the process...

MikeA