Correlated Subqueries

• hon1980

  Valued Member

  Points: 61

  More actions

  September 29, 2010 at 9:01 pm

  #239068

  Hi,

  I'm learning SQL Server from a beginner's book. Wonder if someone could help me understand the following Correlated Subqueries code better. I understand it, but I don't have a strong grasp of it.

  Thanks!

  SELECT soh1.CustomerID, soh1.SalesOrderID, soh1.OrderDate

  FROM Sales.SalesOrderHeader soh1

  WHERE soh1.OrderDate = (SELECT Min(soh2.OrderDate) FROM Sales.SalesOrderHeader soh2 WHERE soh2.CustomerID = soh1.CustomerID)

  ORDER BY CustomerID;

• Lowell

  SSC Guru

  Points: 323547

  More actions

  September 29, 2010 at 9:28 pm

  #1229443

  i hope i can explain this well...

  a correlated sub query is where a column from the OUTER query is referenced in the sub query to limit the results:

  SELECT

  soh1.CustomerID,

  soh1.SalesOrderID,

  soh1.OrderDate

  FROM Sales.SalesOrderHeader soh1

  WHERE soh1.OrderDate = (SELECT Min(soh2.OrderDate)

  FROM Sales.SalesOrderHeader soh2

  WHERE soh2.CustomerID = soh1.CustomerID)

  ORDER BY CustomerID;

  in your example, the aliased table soh1 is not in the FROM/JOINS of the inner query. so a "correlated sub query" needs an outer query and an inner query. As I tried to state above, the reference to the outer queries column limits the results of that inner query to just the items that match.

  your specific example could be re-written as an INNER JOIN:

  SELECT

  soh1.CustomerID,

  soh1.SalesOrderID,

  soh1.OrderDate

  FROM Sales.SalesOrderHeader soh1

  INNER JOIN

  (SELECT

  soh2.CustomerID,

  Min(soh2.OrderDate) As OrderDate

  FROM Sales.SalesOrderHeader soh2

  GROUP BY soh2.CustomerID ) MyAlias

  ON soh2.CustomerID = MyAlias.CustomerID

  WHERE soh1.OrderDate = MyAlias.OrderDate

  ORDER BY CustomerID;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!