SQL Server Database Administrator Interview Question:

What is a correlated sub-query?

Submitted by: Muhammad

A correlated sub-query is a nested query that is linked to the outer query. For instance, say I wanted to find all the employees who have not entered their time for the week. I could query the Employee table to get their first and last name, but I need to look at the TimeEntry table to see if they've entered their time or not. I can't do a straight join here because I'm looking for the absence of time data, so I'll do a correlated sub-query similar to this:

SELECT FirstName, LastName

FROM EMPLOYEE e

WHERE NOT EXISTS (SELECT 1 FROM TimeEntry te

WHERE te.EmpID = e.EmpID

AND te.WeekID = 35)

Notice that the inner query relates to the outer query on the employee ID, thus making it a correlated sub-query. The inner query will be evaluated once per outer query row.
Submitted by: Muhammad