Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
What is Correlated Subquery?
02-14-2013, 05:38 AM
Post: #1
What is Correlated Subquery?
Correlated Subquery
Quote:A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query.

Correlated Subquery vs. Normal Subquery
Quote:The correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself.

Quote:Suppose you want a report of all "OrderID's" where the customer did not purchase more than 10% of the average quantity sold for a given product. This way you could review these orders, and possibly contact the customers, to help determine if there was a reason for the low quantity order. A correlated subquery in a WHERE clause can help you produce this report. Here is a SELECT statement that produces the desired list of "OrderID's":
select distinct OrderId
  from Northwind.dbo.[Order Details] OD
    Quantity <l; (select avg(Quantity) * .1
                      from Northwind.dbo.[Order Details]
                      where OD.ProductID = ProductID)

View Asad Ali's profile on LinkedIn


User(s) browsing this thread: 1 Guest(s)