Subquery vs Join in Oracle

If you retrieve one table data based on another table, then you should use subquery,(I want to retrive only employee details who are those in department 'Acct', joins are using two or more than table retrieve data(ename,deptno,dname,loc,sal,job).

To improve the performance of the query better we use subqueries as well as to handle the unknown condition. Joins used to retrieve a data from one or more tables.

To retrieve the data form more than one table by using single query we use joins and queries. By using queries and joins performance of the query is very fast.

In most cases JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.In JOINs RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.

Joins better than sub queries retrieve data very fast. b.coz if we create cluster and index on common column of tables.