Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 19A7DB5D80B
	for <pgsql-docs-postgresql.org@mail.postgresql.org>;
	Mon, 13 Jun 2011 18:47:40 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 86370-01 for
	<pgsql-docs-postgresql.org@mail.postgresql.org>;
	Mon, 13 Jun 2011 21:47:33 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 97CD0B5D803
	for <pgsql-docs@postgresql.org>; Mon, 13 Jun 2011 18:47:33 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Mon, 13 Jun 2011 16:47:32 -0500
Message-Id: <4DF63F20020000250003E56D@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Mon, 13 Jun 2011 16:47:28 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Grzegorz Szpetkowski" <gszpetkowski@gmail.com>,
	"Robert Haas" <robertmhaas@gmail.com>
Cc: <pgsql-docs@postgresql.org>
Subject: Re: 7.2. Table Expressions FULL join is only supported
	with merge-joinable join conditions
References: <BANLkTinQE8iZXmSvnx4GaXbDbmyK7vC+-w@mail.gmail.com>
	<BANLkTik2bSEyHpE9QKzL_t5w_L4OV-v9CA@mail.gmail.com>
In-Reply-To: <BANLkTik2bSEyHpE9QKzL_t5w_L4OV-v9CA@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201106/59
X-Sequence-Number: 6833

Robert Haas <robertmhaas@gmail.com> wrote:
> Grzegorz Szpetkowski  <gszpetkowski@gmail.com> wrote:

>> "The join condition specified with ON can also contain conditions
>> that do not relate directly to the join."

I think the trouble starts with that sentence, which I believe to be
completely false and misleading.

Simplifying a real-life instance of such confusion among our
programmers:

SELECT <Party columns>, Demographic.dob
  FROM Party LEFT JOIN Demographic
  ON (<Party.pkcols = Demographic.pkcols>
        AND Demographic.dod is NULL);

Which makes absence of date of death part of the outer join
criteria.  So you get all the parties, dead or alive; and only show
date of birth for those not known to be dead.  What they really
wanted to do was exclude parties known to be dead, and for those
parties listed, show date of birth if available.  So they wanted:

SELECT <Party columns>, Demographic.dob
  FROM Party LEFT JOIN Demographic
  ON (<Party.pkcols = Demographic.pkcols>)
  WHERE Demographic.dod is NULL;

Conditions in the ON clause *do* relate to the JOIN -- it's just
that the join might be on conditions other than primary key
equality.

Let's not contribute to muddy thinking by making incorrect
statements like that.

> I don't have a clear feeling for exactly what is needed.

I think the thing which is most likely to surprise people is that
the result can contain rows which are not in the Cartesian product
of joining the two relations.  We might want to point that out,
mention that it's an OUTER JOIN in *both* directions, and maybe give
an example which is half-way plausible as a use-case.  Maybe
something similar to:

test=# create table n_en (n int, word text);
CREATE TABLE
test=# create table n_de (n int, wort text);
CREATE TABLE
test=# insert into n_en values (1,'one'),(2,'two');
INSERT 0 2
test=# insert into n_de values (2, 'zwei'),(3,'drei');
INSERT 0 2
test=# select * from n_en full join n_de using (n);
 n | word | wort
---+------+------
 1 | one  |
 2 | two  | zwei
 3 |      | drei
(3 rows)

And that works to show the difference between:

test=# select * from n_en full join n_de
test-# on (n_en.n = n_de.n and n_de.n > 2);
 n | word | n | wort
---+------+---+------
 1 | one  |   |
 2 | two  |   |
   |      | 2 | zwei
   |      | 3 | drei
(4 rows)

and:

test=# select * from n_en full join n_de
test-# on (n_en.n = n_de.n) where n_de.n > 2;
 n | word | n | wort
---+------+---+------
   |      | 3 | drei
(1 row)

-Kevin