Home » SQL & PL/SQL » SQL & PL/SQL » Join or subquery, which is faster
Join or subquery, which is faster [message #261838] Thu, 23 August 2007 14:44 Go to next message
ajreynolds
Messages: 10
Registered: March 2005
Junior Member
Greetings all,

I was wondering which will give me better performance:

select x,y,z from table1 where exists (select 'X' from table2 where table2.x=table1.x)

or

select a.x,a.y,a.z from table1 a, table2 b where b.x=a.x

The first is easier to read, at least to my mind, but is there going to be any speed difference between the two?

And one other question, is doing something like "select 'X'" in the subquery the best way to do it? Or should I be selecting actual fields for the table for fastest response?

Thanks,

Andy

[Updated on: Thu, 23 August 2007 14:46]

Report message to a moderator

Re: Join or subquery, which is faster [message #261839 is a reply to message #261838] Thu, 23 August 2007 14:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I would run an explain plan on both and analyze the results. I would also run the both set set timing on and see the results.
Re: Join or subquery, which is faster [message #261921 is a reply to message #261838] Fri, 24 August 2007 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As always it depends on the case.
So gather statistics for your objects and let the optimizer find itself the good way. Don't mind it will rewrite your query if you don't choose the optimised form.

As a rule of thumb, not for performances but for a clear query, if you don't need the columns of the second table use "exists", if you need them, of course use join.

In your example, you only use table a column in output columns, so use "exists".

Regards
Michel
Re: Join or subquery, which is faster [message #262081 is a reply to message #261921] Fri, 24 August 2007 08:01 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Please note that if x is not unique in table2, then there is a clear functional difference between the two queries. With the exists values of table1 are shown as many times they are in table1. With the join the values of table1 are shown as many times as they occur in table2.
Example:

SQL> SELECT *
  2  FROM   table1
  3  /

         X Y          Z
---------- ---------- ----------
         1 a          a
         2 b          b
         3 c          c
         4 d          d
         5 e          e
         6 f          f
         7 g          g
         8 h          h
         9 i          i

9 rows selected

SQL> 
SQL> SELECT *
  2  FROM   table2
  3  /

         X
----------
         1
         2
         4
         5
         7
         7
         8
         8

8 rows selected

SQL> 
SQL> SELECT x
  2        ,y
  3        ,z
  4  FROM   table1
  5  WHERE  EXISTS (SELECT 1
  6          FROM   table2
  7          WHERE  table1.x = table2.x)
  8  /

         X Y          Z
---------- ---------- ----------
         1 a          a
         2 b          b
         4 d          d
         5 e          e
         7 g          g
         8 h          h

6 rows selected

SQL> 
SQL> SELECT table1.x
  2        ,y
  3        ,z
  4  FROM   table1
  5        ,table2
  6  WHERE  table1.x = table2.x
  7  /

         X Y          Z
---------- ---------- ----------
         1 a          a
         2 b          b
         4 d          d
         5 e          e
         7 g          g
         7 g          g
         8 h          h
         8 h          h

8 rows selected

SQL> 
Re: Join or subquery, which is faster [message #262085 is a reply to message #262081] Fri, 24 August 2007 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good point, Sabine! Thumbs Up

Regards
Michel
Re: Join or subquery, which is faster [message #262171 is a reply to message #261838] Fri, 24 August 2007 15:15 Go to previous messageGo to next message
ajreynolds
Messages: 10
Registered: March 2005
Junior Member
In the system I'm working on, each record is unique in table2, but there may be multiple records for x in table1.

So, it sounds like performance wise it really doesn't matter which form is used, correct?

How about the second part of my question, is using "select 'X'" in the exists portion just as good performance wise as actually selecting real fields from the table.

Thanks.

Andy
Re: Join or subquery, which is faster [message #262173 is a reply to message #262171] Fri, 24 August 2007 15:21 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you don't care about the columns just use "exists (select null from ...".
There it is clear that you don't care of the return value.

Regards
Michel
Previous Topic: write pl/sql result in xl sheet
Next Topic: ORA-00979: not a GROUP BY expression
Goto Forum:
  


Current Time: Sat Dec 03 13:58:29 CST 2016

Total time taken to generate the page: 0.06593 seconds