Subquery vs. a Join

Last Post 01 Feb 2008 02:27 PM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
kjmcsd
New Member
New Member

--
25 Jan 2008 10:26 AM
I read that SQL server turn subqueries into inner joins. So is there any advantage of doing it one way or the other or is it just preference?

Thanks!
SQLUSA
New Member
New Member

--
01 Feb 2008 02:27 PM
You cannot turn ALL queries with subqueries into JOINs.

Example: correlated subquery

SELECT Subcategory=sc.Name,
AboveAvgListPrice=p1.Name
FROM Production.Product p1
INNER JOIN Production.ProductSubcategory sc
ON sc.ProductSubcategoryID = p1.ProductSubcategoryID
WHERE p1.ListPrice >
(SELECT AVG (p2.ListPrice)
FROM Production.Product p2
WHERE p1.ProductSubcategoryID = p2.ProductSubcategoryID)
ORDER BY Subcategory, AboveAvgListPrice

However, you can turn some into JOINS when subquery was really not forced.

SQL Server query engine turns the query into an actual program based on optimization algorithms. The execution plan includes several steps and intermediate (temp) tables.

Kalman Toth, Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/
You are not authorized to post a reply.

Acceptable Use Policy