Difference between Subquery and Correlated Subquery

Question

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)  T

Also, can someone state the difference between the both

Answer 1

Correlated Subquery is a sub-query that uses values from the outer query. In this case the inner query has to be executed for every row of outer query.

See example here http://en.wikipedia.org/wiki/Correlated_subquery

Simple subquery doesn't use values from the outer query and is being calculated only once:

SELECT id, first_name 
FROM student_details 
WHERE id IN (SELECT student_id
FROM student_subjects 
WHERE subject= 'Science'); 

CoRelated Subquery Example -

Query To Find all employees whose salary is above average for their department

 SELECT employee_number, name
       FROM employees emp
       WHERE salary > (
         SELECT AVG(salary)
           FROM employees
           WHERE department = emp.department);