Re: [HACKERS] JOIN syntax. Examples?

Thomas G. Lockhart wrote:
>
> Well, I've started looking through my books for info on joins. The cross
> join was pretty easy:
>
> postgres=> select * from (a cross join b);
> i| j|i| k
> -+----+-+--
> 1|10.1|1|-1
> 2|20.2|1|-1
> 4| |1|-1
> <snip>
>
> which I've put into my copy of the parser.
>
> Does anyone have a commercial installation which has good support for
> SQL92 joins? I'd like to send some small test cases to verify that I
> understand what the behavior should be.
>
> Also, if anyone has worked with join syntax, outer joins especially, it
> would be great to get some test case contributions...

You could use MS Access.

It has terrible outer join syntax (compared to Oracle), but I quess it
is more or less what SQL92 standard prescribes ;(

To make the queries, just open the query builder and join the tables,
then double-click on the join and set the property not to require all
from one of the tables and only matching from the other.

Here is whar it produced for me:

SELECT account.account_nr, domestic_po.bank_order_id
FROM account LEFT JOIN domestic_po ON account.account_nr =
domestic_po.account_nr;

this requires all fields from account and matching fields from
domestic_po

the oracle equivalent would be:

SELECT account.account_nr, domestic_po.bank_order_id
FROM account
WHERE account.account_nr = (+) domestic_po.account_nr;

BTW, I do think Oracle syntax to be much clearer, but I'm not sure
if we should allow it as and additional syntax.

----------------
Hannu