Обсуждение: JOIN syntax. Examples?

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...
                     - Tom

On Fri, 11 Dec 1998, 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...

I have access to several different versions of Oracle at work...if you
want to send me your test cases, go for it...work email is
'marc.fournier@acadiau.ca'...

Marc G. Fournier                                
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

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

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

Do you know of any place I can look up Oracle's syntax? Or if not, could
I send you a (small) regression test for joins and have you translate
that to Oracle's syntax?

This brings up a question: I would guess that Oracle claims to conform
to SQL92 (or to be compliant, or to be an extended subset, or ??). afaik
this means for many companies that they conform to the "Entry Level"
part of SQL92. What are Oracle's claims on the subject? I'd like to know
where we stand on this relative to them; I've claimed that we are an
"extended subset" but perhaps we are closer than we think...
                 - Tom

On Fri, Dec 11, 1998 at 06:35:39AM +0000, Thomas G. Lockhart wrote:
> 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.

Would Oracle qualify? I hope to get the Linux trial version next week.

> Also, if anyone has worked with join syntax, outer joins especially, it
> would be great to get some test case contributions...

Hmm Oracle's version of outer joins is completely different from Informix.

Michael
-- 
Dr. Michael Meskes, Manager of the Western Branch Office, Datenrevision GmbH
work: Cuxhavener Str. 36, D-21149 Hamburg, Michael.Meskes@datenrevision.de
home: Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!