Click here to Skip to main content
15,311,561 members
Please Sign up or sign in to vote.
1.44/5 (2 votes)
See more:
Is the following piece of SQL Query a normal query or a Correlated Subquery ??

select UserID,
                                          FirstName,
                                          LastName,
                                          DOB,
                                          GFName,
                                          GLName,                                      
                                          LoginName,
                                          LoginEffectiveDate,
                                          LoginExpiryDate,
                                          Password,
                                          Email,
                                          ReportingTo,
                                          Mobile,
                                          CommunicationPreference,
                                          IsActive
                                          from (
                                          select row_number() over (order by FirstName) as Row,
                                          UserID,
                                          FirstName,
                                          LastName,
                                          DOB,
                                          GFName,
                                          GLName,
                                          LoginName,
                                          LoginEffectiveDate,
                                          LoginExpiryDate,
                                          Password,
                                          Email,
                                          ReportingTo,                                    
                                          Mobile,
                                          CommunicationPreference,
                                          IsActive
                                          from DivakarUserRegistration 



Also, can someone state the difference between the both
Posted
Updated 18-Nov-16 2:46am
Comments
Divakar Raj M 24-Jun-13 2:52am
   
I heard from my collegues that it is a correlated subquery. But i am not able to understand how, since the inner query runs independently.

Subquery :- The inner query is executed only once The inner query will get executed first and the output of the inner query used by the outer query.The inner query is not dependent on outer query.

Eg:- SELECT cust_name, dept_no FROM Customer WHERE cust_name IN (SELECT cust_name FROM Customer);

Correlated subquery:-The outer query will get executed first and for every row of outer query, inner query will get executed. So the inner query will get executed as many times as no.of rows in result of the outer query.The outer query output can use the inner query output for comparison. This means inner query and outer query dependent on each other

Eg:- SELECT cust_name,dept_id FROM Cust
WHERE cust_name in (SELECT cust_name FROM dept WHERE cust.dept_id=dept.dept_id);
   
Comments
Divakar Raj M 24-Jun-13 4:51am
   
@Vedant I heard from my collegues that the query that i posted is a correlated subquery. But i am not able to understand how, since the inner query runs independently.
The above query is a subquery.

Reason: The inner query can be independent which does not depend on the outer query and hence it is not Correlated Subquery.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900