Skip site navigation (1) Skip section navigation (2)

Re: Help me to understand (documentation)

From: Joshua Tolley <eggyknap(at)gmail(dot)com>
To: Виктор Вислобоков <corochoone(at)gmail(dot)com>
Cc: PostgreSQL-documentation <pgsql-docs(at)postgresql(dot)org>
Subject: Re: Help me to understand (documentation)
Date: 2009-08-16 03:05:18
Message-ID: 20090816030518.GE3940@eddie (view raw or flat)
Thread:
Lists: pgsql-docs
On Sat, Aug 15, 2009 at 09:29:48PM +0400, Виктор Вислобоков wrote:
>    Hello.
>    I translating the PostgreSQL manual and I run into the problem. I don't
>    understand this:
> 
>    ---------------------From
>    queries.sgml--------------------------------------------------------------------------------
>    The <literal>ON</> or <literal>USING</> clause of an outer join is
>    <emphasis>not</> equivalent
>     to a <literal>WHERE</> condition, because it results in the addition of
>    rows (for unmatched input
>     rows) as well as the removal of rows in the final result.
>    --------------------------------------------------------------------------------------------------------------------------------
> 
>    I don't understand the rest of the phrase after "because".
>    Help me please. Please write same, but in other words, may be I'll
>    understand in this case.
> 
>    With best wishes,
>    Victor Vislobokov
>    St.Peterburg, Russia

Perhaps this will do:

Assume you've two tables:

5432 josh(at)josh*# select * from foo;
 id | data  
----+-------
  1 | data1
  2 | data2
  3 | data3
(3 rows)

5432 josh(at)josh*# select * from bar;
 id | data  
----+-------
  5 | data5
  4 | data4
  3 | data3
(3 rows)

The following two queries use inner joins, and are equivalent:

5432 josh(at)josh*# select * from foo join bar using (id);
 id | data  | data  
----+-------+-------
  3 | data3 | data3
(1 row)

5432 josh(at)josh*# select * from foo, bar where foo.id = bar.id;
 id | data  | id | data  
----+-------+----+-------
  3 | data3 |  3 | data3
(1 row)

In other words, with an INNER join, you can convert the INNER join to a CROSS
JOIN (the comma, in this case, means "CROSS JOIN"), convert the USING or ON
clause into a WHERE clause, and achieve the same result.

However, if I change it to a LEFT join (a RIGHT join or FULL join would
perform similarly, though of course the data returned would differ somewhat):

5432 josh(at)josh*# select * from foo left join bar using (id);
 id | data  | data
----+-------+-------
  1 | data1 |
  2 | data2 |
  3 | data3 | data3
(3 rows)

The sentence you're having trouble with is trying to say that you can't
convert the LEFT join to a CROSS join, change the USING or ON clause to a
WHERE clause, and achieve the same result, because you couldn't get the NULL
values in the result set. I hope this helps.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

In response to

Responses

pgsql-docs by date

Next:From: Виктор ВислобоковDate: 2009-08-16 07:40:21
Subject: Re: Help me to understand (documentation)
Previous:From: Виктор ВислобоковDate: 2009-08-15 17:29:48
Subject: Help me to understand (documentation)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group