Thread: INNER JOINS in sql-select.html

Hi.

From <doc/html/sql-select.html>:
| A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same
| as you get from listing the two items at the top level of FROM. CROSS
| JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are
| removed by qualification. These join types are just a notational
| convenience, since they do nothing you couldn't do with plain FROM
| and WHERE.

Is there really no difference between these two queries?

SELECT blarg
  FROM ta, tb, tc, [...]
 WHERE ta.foo = tb.bar
   AND tb.bar = tc.baz
   AND [...]

SELECT blarg
  FROM ta
  JOIN tb ON tb.bar = ta.foo
  JOIN tc ON tc.baz = tb.bar
  JOIN [...]

I thought that by using the second form, you would be able to do
'explicit' joins, effectivly telling the planner in which order to
join multiple tables (in case you have to join 10+ tables)?


cheers,
stefan

Stefan Weiss <spaceman-4b9f8-20030703@ausgehaucht.sensenmann.at> writes:
> From <doc/html/sql-select.html>:
> | A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same
> | as you get from listing the two items at the top level of FROM. CROSS
> | JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are
> | removed by qualification.

> I thought that by using the second form, you would be able to do
> 'explicit' joins, effectivly telling the planner in which order to
> join multiple tables (in case you have to join 10+ tables)?

They are semantically equivalent, but not necessarily the same from a
performance point of view.  The potential performance issues are covered
elsewhere; I think it would just obfuscate matters to try to include
that topic here.

            regards, tom lane

At 5:56 PM -0500 10/30/03, Tom Lane wrote:
>Stefan Weiss <spaceman-4b9f8-20030703@ausgehaucht.sensenmann.at> writes:
>>  From <doc/html/sql-select.html>:
>  > | A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same
>>  | as you get from listing the two items at the top level of FROM. CROSS
>>  | JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are
>>  | removed by qualification.
>
>>  I thought that by using the second form, you would be able to do
>>  'explicit' joins, effectivly telling the planner in which order to
>>  join multiple tables (in case you have to join 10+ tables)?
>
>They are semantically equivalent, but not necessarily the same from a
>performance point of view.  The potential performance issues are covered
>elsewhere; I think it would just obfuscate matters to try to include
>that topic here.

You can imply the issue without obfuscating things.  How about:

>A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same
>as you get from listing the two items at the top level of FROM.
>CROSS JOIN yields the same results as INNER JOIN ON (TRUE), that is,
>no rows are removed by qualification.

--
The opinions expressed in this message are mine,
not those of Caltech, JPL, NASA, or the US Government.
Henry.B.Hotz@jpl.nasa.gov, or hbhotz@oxy.edu

"Henry B. Hotz" <hotz@jpl.nasa.gov> writes:
> You can imply the issue without obfuscating things.  How about:

> A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same
> as you get from listing the two items at the top level of FROM.
> CROSS JOIN yields the same results as INNER JOIN ON (TRUE), that is,
> no rows are removed by qualification.

Okay, but that doesn't do the trick --- it implies that CROSS JOIN isn't
equivalent to INNER JOIN ON (TRUE), when in fact they are equivalent,
both as to result and performance characteristics.  The issue at hand is
that an explicit "a JOIN b" may not be equivalent to "FROM a, b".

I reworded the passage as

        CROSS JOIN and INNER JOIN
        produce a simple Cartesian product, the same result as you get from
        listing the two items at the top level of FROM,
    but restricted by the join condition (if any).
        CROSS JOIN is equivalent to INNER JOIN ON
        (TRUE), that is, no rows are removed by qualification.

does that help?

            regards, tom lane

At 7:38 PM -0500 11/3/03, Tom Lane wrote:
>"Henry B. Hotz" <hotz@jpl.nasa.gov> writes:
>>  You can imply the issue without obfuscating things.  How about:
>
>>  A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same
>>  as you get from listing the two items at the top level of FROM.
>>  CROSS JOIN yields the same results as INNER JOIN ON (TRUE), that is,
>>  no rows are removed by qualification.
>
>Okay, but that doesn't do the trick --- it implies that CROSS JOIN isn't
>equivalent to INNER JOIN ON (TRUE), when in fact they are equivalent,
>both as to result and performance characteristics.  The issue at hand is
>that an explicit "a JOIN b" may not be equivalent to "FROM a, b".
>
>I reworded the passage as
>
>         CROSS JOIN and INNER JOIN
>         produce a simple Cartesian product, the same result as you get from
>         listing the two items at the top level of FROM,
>    but restricted by the join condition (if any).
>         CROSS JOIN is equivalent to INNER JOIN ON
>         (TRUE), that is, no rows are removed by qualification.
>
>does that help?

'sarright.  I was just wordsmithing without worrying about the meaning.
--
The opinions expressed in this message are mine,
not those of Caltech, JPL, NASA, or the US Government.
Henry.B.Hotz@jpl.nasa.gov, or hbhotz@oxy.edu