Thread: Help me to understand (documentation)

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

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

Thanks, Joshua.

I understand the difference JOIN and WHERE, but I'm very appreciate your explanation.
My goal is a bit other, I want to translate the phrase "as is" (in compact form)

With best wishes
Victor Vislobokov
St.Peterburg, Russia

On Sun, Aug 16, 2009 at 11:40:21AM +0400, Виктор Вислобоков wrote:
>    Thanks, Joshua.
>
>    I understand the difference JOIN and WHERE, but I'm very appreciate your
>    explanation.
>    My goal is a bit other, I want to translate the phrase "as is" (in compact
>    form)

The phrase you mentioned indicates that the ON and USING clauses not only
filter out rows, like a WHERE clause would, but also instruct the join to add
NULL values for rows in one relation that aren't matched by rows in the other
relation. Does that help at all? For whatever it's worth, my experience with
translation is that sometimes you've got to pretty much rewrite the original
either because a literal translation doesn't work in the language of choice,
or because the original text didn't make sense in the source language to begin
with. This case may include a little of both :)

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