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
This question is related to
sql
sql-server
subquery
correlated-subquery
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.
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 :
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,
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.
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
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
Source: Stackoverflow.com