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

User · Accepted Answer

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

User · Answer

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

User · Answer

I think below explanation will help to you    differentiation between those  Correlated subquery is an inner query referenced by main query  outer query  such that inner query considered as being excuted repeatedly   non-correlated subquery is a sub query that is an independent of the outer query and it can executed on it s own without relying on main outer query   plain subquery is not dependent on the outer query

User · Answer

CORRELATED SUBQUERIES    Is evaluated for each row processed by the Main query  Execute the Inner query based on the value fetched by the Outer query  Continues till all the values returned by the main query are matched  The INNER Query is driven by the OUTER Query  Ex   SELECT empno fname sal deptid FROM emp e WHERE sal  SELECT AVG sal  FROM emp WHERE deptid e deptid   The Correlated subquery specifically computes the AVG sal  for each department   SUBQUERY       Runs first executed once returns values to be used by the MAIN Query  The OUTER  Query is driven by the INNER QUERY

User · Answer

In an SQL query  if the inner query executes for every row of the outer query  If the inner query is executed for once and the result is consumed by the outer query  then it is called as non co-related query   Correlated sub-queries usually correspond to normal join queries  i e  join queries that have no sub-queries  and can usually be expressed as such  It is sometimes recommended that they are translated into such join queries  because most SQL DBMSs will execute them

User · Answer

A subquery is a select statement that is embedded in a clause of another select statement   EX      select ename  sal  from emp  where sal  gt   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

User · Answer

when it comes to subquery and co-related query both have inner query and outer query the only difference is in subquery the inner query doesn t depend on outer query  whereas in co-related inner query depends on outer

[sql] Difference between Subquery and Correlated Subquery

Examples related to sql

Examples related to sql-server

Examples related to subquery

Examples related to correlated-subquery