public inbox for [email protected]
help / color / mirror / Atom feedANSI join types
2+ messages / 2 participants
[nested] [flat]
* ANSI join types
@ 2002-08-14 03:12 Bruce Momjian <[email protected]>
2002-08-14 04:55 ` Re: ANSI join types Tom Lane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Bruce Momjian @ 2002-08-14 03:12 UTC (permalink / raw)
To: pgsql-docs
I got this list from Gavin Roy's presentation at O'Reilly. Is there a
good spot for this summary?
Also, I can not find documentation on UNION JOINS in our docs.
---------------------------------------------------------------------------
Bruce Momjian wrote:
> SELECT
> * JOINS
>
> * Cross: All combinations of rows are used
> * Inner: Only rows where matches are found are retained
> * Left (Outer): Returns all rows from table A, matched or not, and
> only rows from table B where matches are found
> * Right (Outer): Inverse of Left, returns all rows from table B and
> only matches from table A
> * Full: A combination of left and right. Where matches are not
> found, NULLs fill the columns of the other table
> * Union: This is different than the UNION operator used to merge the
> output of multiple queries. This is the inverse of an Inner, only
> rows are returned when no matches are found
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> [email protected] | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
--
Bruce Momjian | http://candle.pha.pa.us
[email protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: ANSI join types
2002-08-14 03:12 ANSI join types Bruce Momjian <[email protected]>
@ 2002-08-14 04:55 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2002-08-14 04:55 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs
Bruce Momjian <[email protected]> writes:
> Also, I can not find documentation on UNION JOINS in our docs.
There is none because we don't support it. While I took the trouble
to make the parser take it, there's no implementation. I'm not now
excited about making it happen ever, because I read this in SQL99:
Annex D
Deprecated features
It is intended that the following features will be removed at a
later date from a revised version of this part of ISO/IEC 9075:
1) The ability to specify UNION JOIN in a <joined table> has been
deprecated.
BTW, I think the description
> * Union: This is different than the UNION operator used to merge the
> output of multiple queries. This is the inverse of an Inner, only
> rows are returned when no matches are found
is pretty poor. As near as I can tell from the SQL92 spec, "x UNION
JOIN y" is supposed to produce the same result as
(select *,<y.nulls> from x) UNION ALL (select <x.nulls>,* from y)
where <y.nulls> denotes a list of NULLs matching the columnset of y,
and similarly for <x.nulls>. This behavior has nothing to do with
whether any value matches exist between x and y --- it makes no join
comparisons at all.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-08-14 04:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-14 03:12 ANSI join types Bruce Momjian <[email protected]>
2002-08-14 04:55 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox