Home » SQL & PL/SQL » SQL & PL/SQL » costs of joins
costs of joins [message #288725] Tue, 18 December 2007 07:46 Go to next message
Messages: 16
Registered: September 2006
Junior Member
Hi all,

I just wrote two simple query, one using an inner join and the other using a subquery to retrieve the same results.

Here is my example queries:

Query #1:
select   column_1,
from     tableA
         inner join tableB on tableB.id = tableA.id
group by column_1

Query #2:
select   column_1,
from     tableA
where    tableA.id in (select tableB.id from tableB)
group by column_1

I ran the explain plan and found that Query #2 costs considerably less... so my question is this: are subqueries in these type of scenarios always run faster because there is no joins?

I'm just confused because I always thought that subqueries are more expensive than using inner joins?

Any thoughts or opinions on this?

Re: costs of joins [message #288731 is a reply to message #288725] Tue, 18 December 2007 07:55 Go to previous messageGo to next message
Messages: 4766
Registered: February 2005
Location: East Coast USA
Senior Member
Factors that could change the cost would be how many rows are in each of the tables and whether an index exists on id column.
Re: costs of joins [message #288738 is a reply to message #288725] Tue, 18 December 2007 08:05 Go to previous message
Michel Cadot
Messages: 65140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, if you have up to date statistics, optimizer will know which form is cheaper and will transform your query.
As a rule of thumb, if you don't need values from a table, put it in a subquery.

Previous Topic: renaming column at run time
Next Topic: PL/SQL Performance
Goto Forum:

Current Time: Fri Aug 18 03:55:09 CDT 2017

Total time taken to generate the page: 0.07522 seconds