How can I optimize this dynamic SQL query in oracle with PL/SQL?

Differences between sub query and joins in SQL(Oracle)?

• What are the differences between Sub query and Joins in Oracle SQL and when to use the sub query and when to use joins in queries?

• Answer:

  http://asktom.oracle.com/pls/asktom/f?p=100%3A11%3A0%3A%3A%3A%3AP11_QUESTION_ID%3A66812779016023 they (joins/subqueries) are semantically different. Use a subquery when you need no columns from the tables referenced in the subquery. Use a join when you do need some of the columns. select * from emp where deptno in ( select deptno from dept ); would be "better" than select emp.* from emp, dept where emp.deptno = dept.deptno; And remember, a subquery cannot simply be replaced by a join (and vice versa), since they  often result in DIFFERENT ANSWERS. A little further reading: http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries007.htm