While debugging one procedure I came across below Scenario where a sub-query was returning all records from outer query.
CREATE TABLE TABLE1 (COL1 INT IDENTITY )
INSERT INTO TABLE1 DEFAULT VALUES
go 20 -- INSERT 20 RECORDS TO TABL1
CREATE TABLE TABLE2 (COL2 VARCHAR(10))
INSERT INTO TABLE2 VALUES('A'),('B'),('C')
SELECT * FROM TABLE1 WHERE COL1 IN (SELECT COL1 FROM TABLE2)
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.
SELECT * FROM TABLE1 WHERE COL1 IN (SELECT COL1 FROM TABLE2 WHERE CAST(COL1 AS VARCHAR) = COL2)
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.