From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Ross J. Reedstrom <reedstrm@rice.edu>
Cc: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-docs@postgresql.org
Subject: Re: Re: FAQ update for 7.1
Date: Mon, 22 Jan 2001 01:33:52 -0500 (EST)
Message-ID: <200101220633.BAA22888@candle.pha.pa.us> (raw)
In-Reply-To: <20001106111418.B31151@rice.edu>
> On Sat, Nov 04, 2000 at 03:13:26PM -0500, Bruce Momjian wrote:
> > Thanks. I will update it as soon as 7.1 goes beta.
> > >
> > > 4.24) How do I do an outer join?
> > >
> > > Outer joins are supported in 7.1.
> > >
>
> Perhaps throw in an example of the supported syntax? From what I've seen
> discussed here, people coming from other systems will find their outer
> join queries failing, and may end up at this question.
Good idea. Here is an updated FAQ item on outer joins. It will appear
on the web site as soon as I can connect.
---------------------------------------------------------------------------
4.24) How do I do an outer join?
PostgreSQL 7.1 and later supports outer joins. Here are two examples:
SELECT *
FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
or
SELECT *
FROM t1 LEFT OUTER JOIN t2 USING (col);
These identical queries join t1.col to t2.col, and return any unjoined
rows in t1. A RIGHT join would return unjoined
rows of table t2. A FULL join would return unjoined rows from t1 and t2.
The word OUTER is optional and is
assumed in LEFT, RIGHT, and FULL joins. Ordinary joins are called INNER
joins.
In previous releases, outer joins can be simulated using UNION and NOT
IN. For example, when joining tab1 and
tab2, the following query does an outer join of the two tables:
SELECT tab1.col1, tab2.col2
FROM tab1, tab2
WHERE tab1.col1 = tab2.col1
UNION ALL
SELECT tab1.col1, NULL
FROM tab1
WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2)
ORDER BY tab1.col1
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: pgman@candle.pha.pa.us, reedstrm@rice.edu, peter_e@gmx.net
Subject: Re: Re: FAQ update for 7.1
In-Reply-To: <200101220633.BAA22888@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox