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(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