Re: Help me to understand (documentation)

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@josh*# select * from foo;
 id | data
----+-------
  1 | data1
  2 | data2
  3 | data3
(3 rows)

5432 josh@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@josh*# select * from foo join bar using (id);
 id | data  | data
----+-------+-------
  3 | data3 | data3
(1 row)

5432 josh@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@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