I want to find out which products are stocked in both Toronto and Bombay. These are five solutions:

select product_id from inventories where warehouse_id=5
intersect
select product_id from inventories where warehouse_id=9;
select product_id from inventories where warehouse_id=5
and product_id in (select product_id from inventories where warehouse_id=9);
select product_id from inventories i where warehouse_id=5
and exists (select product_id from inventories j where j.warehouse_id=9 and j.product_id=i.product_id);
select distinct product_id from (
(select product_id from inventories where warehouse_id=5)
join
(select product_id from inventories where warehouse_id=9)
using (product_id));
select product_id from
(select product_id from inventories where warehouse_id=5
union all
select product_id from inventories where warehouse_id=9)
group by product_id having count(*) > 1;

To me, the first is the most intuitive: find the products in Toronto and the products in Bombay, and the answer is the intersection. The fifth solution is in effect the same thing done manually: add the two queries together, and keep only those products that occur twice (though there could a bug in that solution - what is it, and how can you avoid it?) The second uses a subquery. The third uses a correlated subquery which is often an inefficient, iterative, structure. The fourth is perhaps the most convoluted.
Which of the five will be the most efficient? Or will the cost based optimizer be able to re-write them into a common, efficient, form? Are there any other solutions?
These are my results:

This surprised me: I had expected the the third solution to be cheapest (assuming that it could be rewritten to a semijoin, as it was) and that the fourth solution would be the worst.
The take away from all this is that the way you write your code can have a huge effect on the way it runs, and you should always consider alternative formulations.
Hope you enjoyed that - you will have if you are as much of a SQL headcase as I am.

Comments

PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.
To Know more visit the :- << moderator edit: link spam removed >>

Database SQL optimizer will generated specific query plan for specific SQL text. There are many potential plans can be generated by database SQL optimizer for a specific SQL text. If you rewrite your SQL with semantically equivalent SQL, the potential query plan may be changed due to the lower cost is estimated for the new text. If you have tool like this
/* link spam removed by moderator */
You will find the secret of how database SQL optimizer works !