Difference between Subquery and Correlated Subquery

by

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

More Related Contents:

  1. Difference between CTE and SubQuery?
  2. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  3. Nested select statement in SQL Server
  4. Is it possible to write single query on two table which are not connected to each other?
  5. select yesterday date in where condition
  6. Import CSV file into SQL Server
  7. How to create a table from select query result in SQL Server 2008 [duplicate]
  8. Are there any disadvantages to always using nvarchar(MAX)?
  9. Creating an index on a table variable
  10. Is there a LastIndexOf in SQL Server?
  11. Get all table names of a particular database by SQL query?
  12. Turn off constraints temporarily (MS SQL)
  13. OR is not supported with CASE Statement in SQL Server
  14. How to set variable from a SQL query?
  15. How do I execute a stored procedure once for each row returned by query?
  16. Case in Select Statement
  17. T-SQL: Selecting Column Based on MAX(Other Column)
  18. How to store image in SQL Server database tables column [duplicate]
  19. How to find third or nᵗʰ maximum salary from salary table?
  20. SQL “between” not inclusive
  21. Benefits Of Using SQL Ordinal Position Notation?
  22. sql group by only rows which are in sequence
  23. SQL Server replace, remove all after certain character
  24. Does UNION ALL guarantee the order of the result set [duplicate]
  25. Inserting rows into a table with one IDENTITY column only [duplicate]
  26. How to find sum of multiple columns in a table in SQL Server 2005?
  27. How to retrieve field names from temporary table (SQL Server 2008)
  28. When using GETDATE() in many places, is it better to use a variable?
  29. Try_Convert for SQL Server 2008 R2
  30. SQL Server variable scope in a stored procedure

Leave a Comment