DBA Routines, Optimizing Queries Which is better a subquery or a join? How can you tell which will be more efficient? Included here is a script which generates two sample tables, tblProduct and tblProductSales. tblProduct and tblProductSales are linked by productId which is a primary key in tblProduct and a foreign key in tblProductSales. The records for each table are generated using a random number generator and a while loop. We can compare methods by utilizing "Client Statistics" and "Actual Execution Plan" which will help to choose the optimal pathway.
According to MSDN, in most cases, there is usually no performance difference between SQL methods that use a sub-query or a join. In fact the execution plan may be identical, meaning that the system is following the same path.
According to MSDN, in some cases where existence must be checked, a join will produce better performance. The nested query will have to be executed once for each row of the outer query. In such cases the join will be the more efficient method.
Generally speaking, join are faster than subqueries, but this will depend on the execution plan generated by SQL Server. If the same execution plan is generated for the sub query as that for the join then the actual execution path will be identical.
We can test differnt SQL methods by using "Client Statistics" and also "Actual Execution Plan" options while running the queries side by side. Do not forget to clear the caches before each test using :
CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO DBCC FREEPROCCACHE; GO
It is much easier to follow a series of JOINS than a series of sub-queries - the JOINS are self documenting Compare the following:
----- subquery vs. join testing
---subquery
select
Id,
Name,
[Description]
from
tblProducts
where Not Exists(select * from tblProductSales where ProductId = tblProducts.Id);
-----compare this to the JOIN which is much easier to read
Select
tblProducts.Id,
Name,
[Description]
from
tblProducts
left join tblProductSales
on tblProducts.Id = tblProductSales.ProductId
where tblProductSales.ProductId IS NULL;