Обсуждение: 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!