While debugging one procedure I came across below Scenario where a sub-query was returning all records from outer query.

Initially I thought it is a bug in SQL server. As COL1 column is not part of Table2 , it should fail binding stage and throw error. But it returned all 20 records from Table1. Though it was not the output what I was expecting. So the reason behind not throwing error is that when we use sub query the outer query expose all its column to inner query.  So it became a correlated sub query and it logically executed the inner query once for every row returned from our query. Below is the actual execution plan of the query.


Now to understand it let me write a correlated sub query.

I am using cast function here just to avoid data type mismatch. Above query will not return anything because there is no matching between Col1 and Col2. But here I wanted to highlight that in the sub query COL1 is accessible. Sometime it is confusing because it looks like  a sub query but in reality the outer query value passed to a correlated sub query.