Friends at the PostgreSQL group:

After a long search* I could not find an answer for the following
question yet:

Which is more performance-friendly: JOIN ON or JOIN USING?

* The word USING is too much used and it ends up in a flood of endless
not-related results.

Thank you in advance for your help.

Kind regards,
Pedro.

Pedro <pedrokarneiro (AT) gmail (DOT) com> wrote:
There's no difference in performance. They're just two different ways
of writing the same thing.

-M-

On Wed, 27 Oct 2010 09:08:20 -0700, Pedro wrote:


Definitely "JOIN ON". The "JOIN USING" needs more typing and I am a
terrible typist.



--
http://mgogala.freehostia.com

On 2010-10-27 20:20, Mladen Gogala wrote:
What is the average length of the column name(s) that you use in the on
clause ;-)

/Lennart

On 27 out, 15:41, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Thank you guys for the fun.
Anyway this is serious for the community that need to set some
standards for the simplicity and benefit of all those that deal with
mission critical and giant databases.
Despite both JOIN ON and JOIN USING are found in ANSI SQL 92, their
executions are performed differently from one another. In my oppinion
there is some metadata query behind the scenes for JOIN USING, but I
will have to proof it.
Back to the lab.
Cheers.

On Thu, 28 Oct 2010 05:08:52 -0700, Pedro wrote:

How so? That is not my experience. Can you present two plans that are
different? I even tested with the following queries:

select e.ename,e.job,e.sal,d.dname,d.loc
from emp e join dept d using (deptno)
order by e.deptno

and

select e.ename,e.job,e.sal,d.dname,d.loc
from emp e join dept d on (e.deptno=d.deptno)
order by e.deptno

The plans were exactly the same:
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------
Merge Join (cost=2.49..2.71 rows=14 width=28) (actual time=0.032..0.041
rows=14 loops=1)
Merge Cond: (d.deptno = e.deptno)
-> Sort (cost=1.08..1.09 rows=4 width=18) (actual time=0.013..0.013
rows=4 loops=1)
Sort Key: d.deptno
Sort Method: quicksort Memory: 25kB
-> Seq Scan on dept d (cost=0.00..1.04 rows=4 width=18)
(actual time=0.003..0.005 rows=4 loops=1)
-> Sort (cost=1.41..1.44 rows=14 width=12) (actual time=0.017..0.020
rows=14 loops=1)
Sort Key: e.deptno
Sort Method: quicksort Memory: 25kB
-> Seq Scan on emp e (cost=0.00..1.14 rows=14 width=12)
(actual time=0.002..0.009 rows=14 loops=1)
Total runtime: 0.082 ms
(11 rows)

QUERY
PLAN
--------------------------------------------------------------------------------------------------------------
Merge Join (cost=2.49..2.71 rows=14 width=28) (actual time=0.031..0.042
rows=14 loops=1)
Merge Cond: (d.deptno = e.deptno)
-> Sort (cost=1.08..1.09 rows=4 width=18) (actual time=0.012..0.012
rows=4 loops=1)
Sort Key: d.deptno
Sort Method: quicksort Memory: 25kB
-> Seq Scan on dept d (cost=0.00..1.04 rows=4 width=18)
(actual time=0.003..0.006 rows=4 loops=1)
-> Sort (cost=1.41..1.44 rows=14 width=12) (actual time=0.017..0.019
rows=14 loops=1)
Sort Key: e.deptno
Sort Method: quicksort Memory: 25kB
-> Seq Scan on emp e (cost=0.00..1.14 rows=14 width=12)
(actual time=0.002..0.006 rows=14 loops=1)
Total runtime: 0.078 ms
(11 rows)


If you have an example of differing plans, I would be very keen to learn
about it. In other words, if you want a serious discussion, please
provide some background information.



--
http://mgogala.freehostia.com

On 2010-10-27, Pedro <pedrokarneiro (AT) gmail (DOT) com> wrote:
"Join on" is faster because the docs sat that "join using" is translated
into "join on". That translation is an extra step
But probably not _measurably_ faster.


otoh: "using" may get you fewer columns in the result set, so that could
be faster if the result set is large and bandwidth not.

--
ɹǝpun uʍop ɯoɹɟ sƃuıʇǝǝɹ⅁

On 2010-11-02 11:22, Jasen Betts wrote:
[...]
How does the join clause affect the number of columns in the result?

/Lennart

On 2 nov, 10:36, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Friends, thank you for the useful information.

On 2010-11-02, Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:
The column(s) joined on only appear(s) once if the request was "select * ..."

--
ɹǝpun uʍop ɯoɹɟ sƃuıʇǝǝɹ⅁