SQL server, write correlated subqueries.

A subquery is a query that is nested into another query. When you work with T-SQL you have different ways to use subqueries, and every method has different performance results.

Our example is a Database with two tables, take a look on the image below:


 In this case we have a main table Employee with a one-to-many relation to the Address table.

Where value IN. (non correlated example)

The first query I want to show you is:

   1: SELECT 
   2:     E.FirstName, E.LastName
   3: FROM 
   4:     Employee AS E
   5: WHERE 
   6:     E.Employee_id IN
   7: (SELECT 
   8:     A.Employee_fk 
   9: FROM Address AS A)

In this case the SELECT will search all the Employee id in the address table and will return a list of id used by the first query to show the employee found. In this case we will not view the employees without and address. This is a non correlated example, because SQL will search before in the Address table, without know nothing about the employee table, than it will match the list of id with the employee table.

Where value EXISTS. (correlated example)

The second query will use the EXISTS clause:

   1: SELECT 
   2:     E.FirstName, E.LastName
   3: FROM 
   4:     Employee AS E
   6: (SELECT 
   7:     A.Employee_fk 
   8: FROM 
   9:     Address AS A
  10: WHERE 
  11:     E.Employee_id = A.EMployee_fk)

In this case the query is using a correlated value from the outer query. The inner query match the id’s with the id’s in the outer query.

The performance for this queries are totally different if you are working with thousand of rows.

To understand which one is better, you should run both queries with an execution plan and try to build the right clustered index to run the most performing query.