Tuesday, April 26, 2016

How do you explain this correlated subquery?

This post is to explain the processing of a correlated subquery as this type of subquery is  not so straight forward.

Non-correlated subquery is easy to understand as the inner query is evaluated first and then the outer query is evaluated using the value(s) returned by the inner query.

In the case of correlated subquery, the outer query is run first and then the inner query is run for each value of outer query to find the match for the value returned.

This example is chosen to explain the processing of a correlated subquery as the number of outer evaluations is quite small (only 9 employees). This is a query to the Employees table in the Northwind database in SQL Server.

This query is trying to find the employees living in a city from where the orders were also shipped.



This returns the following response:

Of the 9 employees only 6 of the above employees lived in the same city where the orders were shipped.

Now review this tutorial created using Sway to follow the processing:
https://docs.com/jayaram-krishnaswamy/4697/undestanding-the-processing of a correlated