0

I have two queries for the same task

ONE:

select * from table1 t1 
INNER JOIN table2 t2 
ON t1.id=t2.id

TWO

select * from table1 t1 
INNER JOIN (select * from table2) t2 
ON t1.id=t2.id

I checked the execution plan for both the queries.Both execution plans are same.But i doubt ,is there any difference in both the queries? If yes which one is more efficient?

gkarya42
  • 429
  • 6
  • 22
  • Add which DBMS to the tags. It will help you get the particular DB users to notice your question faster. Also to answer your query, the information is almost always needed – toddlermenot Sep 14 '15 at 16:42

1 Answers1

0

You haven't mentioned which DBMS. SQL is just declarative - you tell Oracle(or any other RDBMS) what you want. But the Execution Plan is what ultimately decides how the query will be executed. So if the plans of both queries are the same, then you can be rest assured there will be no difference in performance. Both queries will be executing ditto as far as the RDBMS is concerned.

Even though both queries are the same, the first one is the most preferred/right way to do it. The second method means the RDBMS needs to do a FULL scan on table2 before joining but Oracle's CBO is usually smart enough to rewrite the 2nd one to be the same as 1st one. This is something you need to be aware of. Some RDBMS have powerful optimizers that rewrite your query before even deriving the plan if it reduces the executedion cost of the query.

toddlermenot
  • 1,588
  • 2
  • 17
  • 33