public inbox for [email protected]  

help / color / mirror / Atom feed

From: Robert Haas <[email protected]>
To: Grzegorz Szpetkowski <[email protected]>
Cc: [email protected]
Subject: Re: 7.2. Table Expressions FULL join is only supported with merge-joinable join conditions
Date: Mon, 13 Jun 2011 12:07:36 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Mon, May 16, 2011 at 7:32 PM, Grzegorz Szpetkowski
<[email protected]> wrote:
> http://www.postgresql.org/docs/9.0/static/queries-table-expressions.html
>
> "The join condition specified with ON can also contain conditions that
> do not relate directly to the join. This can prove useful for some
> queries but needs to be thought out carefully. For example:
>
> => SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';"
>
> Can you add information about FULL JOIN in doc there ? I am trying to
> run such query:
>
> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
> ERROR:  FULL JOIN is only supported with merge-joinable join conditions
>
> I mean add something like: "Note that you can't use such conditions
> with FULL JOIN, only equality of selected columns is supported with
> this type".

This works in 9.1.  A possibly relevant point is that the semantics
are not what you might think:

rhaas=# create table t1 (num int, value text);
CREATE TABLE
rhaas=# create table t2 (num int, value text);
CREATE TABLE
rhaas=# insert into t1 values (1, 'yyy'), (2, 'yyy');
INSERT 0 2
rhaas=# insert into t2 values (2, 'yyy'), (3, 'yyy');
INSERT 0 2
rhaas=# select * from t1 full join t2 on t1.num = t2.num and t2.value = 'xxx';
 num | value | num | value
-----+-------+-----+-------
   1 | yyy   |     |
   2 | yyy   |     |
     |       |   2 | yyy
     |       |   3 | yyy
(4 rows)

It's very possible that a user who is writing this meant one of the following:

select * from t1 full join (select * from t2 where t2.value = 'xxx')
t2 on t1.num = t2.num;
select * from t1 full join t2 on t1.num = t2.num WHERE t2.value = 'xxx';

...which are not equivalent to each other, or to the original query.
It'd be nice to document this better, but I don't have a clear feeling
for exactly what is needed.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

view thread (3+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: 7.2. Table Expressions FULL join is only supported with merge-joinable join conditions
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox