Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] JOIN syntax. Examples?

From: Hannu Krosing <hannu(at)trust(dot)ee>
To: "Thomas G(dot) Lockhart" <lockhart(at)alumni(dot)caltech(dot)edu>
Cc: Postgres Hackers List <hackers(at)postgreSQL(dot)org>
Subject: Re: [HACKERS] JOIN syntax. Examples?
Date: 1998-12-11 11:07:10
Message-ID: 3670FCDE.658C386@trust.ee (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: D'Arcy J.M. CainDate: 1998-12-11 12:34:58
Subject: Announce: PyGreSQL 2.2
Previous:From: Zeugswetter Andreas IZ5Date: 1998-12-11 09:26:19
Subject: AW: [HACKERS] JOIN syntax. Examples?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group