Difference between Subquery and Correlated Subquery

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 E1WHERE 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

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

A subquery is a select statement that is embedded in a clause of another select statement.

EX:

select ename, sal from emp  where sal > (select sal                        from emp where ename ='FORD');

A Correlated subquery is a subquery that is evaluated once for each row processed by the outer query or main query. Execute the Inner query based on the value fetched by the Outer query all the values returned by the main query are matched. The INNER Query is driven by the OUTER Query.

Ex:

select empno,sal,deptid from emp e where sal=(select avg(sal)            from emp where deptid=e.deptid);

DIFFERENCE

The inner query executes first and finds a value, the outer query executes once using the value from the inner query (subquery)

Fetch by the outer query, execute the inner query using the value of the outer query, use the values resulting from the inner query to qualify or disqualify the outer query (correlated)

For more information :http://www.oraclegeneration.com/2014/01/sql-interview-questions.html