SQL Server Database Administrator Question:
Download Questions PDF

What is a correlated sub-query?

Answer:

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.

Download SQL Server DB Administrator Interview Questions And Answers PDF

Previous QuestionNext Question
Is SQL Server implemented as a service or an application?What are the steps to take to improve performance of a poor performing query?