Join vs Correlated Sub-query

Not always but I observed simple join is much better than Correlated sub-query. So plz avoid to use it, if we have option than ....today i am sharing how a simple join is better than correlated sub query..

correlated sub-query in columns

select  candidate_id,last_name,first_name,client_id, (select name from client_master where client_id=candidate_master.client_id) as cl_name

 from candidate_master

 Output 

(291 row(s) affected)

Stats

Table 'candidate_master'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'client_master'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 107 ms.

 Left outer join 

SELECT        candidate_master.Candidate_ID, candidate_master.last_name, candidate_master.first_name, candidate_master.Client_ID, client_master.name

FROM            candidate_master LEFT OUTER JOIN

                         client_master ON candidate_master.Client_ID = client_master.Client_ID

 Output

(291 row(s) affected)

Stats

Table 'candidate_master'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'client_master'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 70 ms.

Analysis

comparison show how left outer is much better than correlated sub-query. So plz try to avoid this if we have option in simple way.

If you liked this post, do like my page on https://www.facebook.com/pages/Sql-DBAcoin/523110684456757