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

Above example is not Co-related Sub-Query. It is Derived Table / Inline-View since i.e, a Sub-query within FROM Clause.

A Corelated Sub-query should refer its parent(main Query) Table in it. For example See find the Nth max salary by Co-related Sub-query:

SELECT Salary  FROM Employee E1 WHERE N-1 = (SELECT COUNT(*)              FROM Employee E2              WHERE E1.salary <E2.Salary)  

Co-Related Vs Nested-SubQueries.

Technical difference between Normal Sub-query and Co-related sub-query are:

1. Looping: Co-related sub-query loop under main-query; whereas nested not; therefore co-related sub-query executes on each iteration of main query. Whereas in case of Nested-query; subquery executes first then outer query executes next. Hence, the maximum no. of executes are NXM for correlated subquery and N+M for subquery.

2. Dependency(Inner to Outer vs Outer to Inner): In the case of co-related subquery, inner query depends on outer query for processing whereas in normal sub-query, Outer query depends on inner query.

3.Performance: Using Co-related sub-query performance decreases, since, it performs NXM iterations instead of N+M iterations. ¨ Co-related Sub-query Execution.

For more information with examples :

http://dotnetauthorities.blogspot.in/2013/12/Microsoft-SQL-Server-Training-Online-Learning-Classes-Sql-Sub-Queries-Nested-Co-related.html

Answer 2

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