SQL : Joins and Corelated Subquery

Co-related Subquery

A sub query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub query. A sub query can be used anywhere an expression is allowed


/* SELECT statement built using a sub query. */
SELECT Name
FROM AdventureWorks2008R2.Production.Product
WHERE ListPrice =
(SELECT ListPrice
FROM AdventureWorks2008R2.Production.Product
WHERE Name = 'Chainring Bolts' );


/* SELECT statement built using a join that returns
the same result set. */
SELECT Prd1. Name
FROM AdventureWorks2008R2.Production.Product AS Prd1
JOIN AdventureWorks2008R2.Production.Product AS Prd2
ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2. Name = 'Chainring Bolts';

In some cases they can rank among the poorest performing SQL statements imaginable because the inner result set must be constructed for every single row that is a candidate for inclusion in the outer result set. If both the inner and outer result sets are large the amount of processing required can be huge
If a table appears only in a sub query and not in the outer query, then columns from that table cannot be included in the output.


Conclusion
There are times when a subquery is advisable, but in most cases a JOIN is better. Here is why:

1. Subqueries have to execute first, then the outer query is executed. Basically executing one subquery within your query means you are executing TWO queries instead of one. Joins would eliminate the need for this.

2. A join can take better advantage of the index. If your table is indexed on the joined column, a join is much faster.

3. For ease of reading, joins are much better.