Sub Query behaviour looks like a bug

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 )
GO
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.

subquery

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.

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

2 Comments on “Sub Query behaviour looks like a bug”

    1. Yes you are right. If you are using Where EXISTS the query is clearly identified as a correlated Sub query. Here in my example I have used IN clause which sometimes confuse developers and they think it is a nested query.

Leave a Reply to Sandip Pani Cancel reply

Your email address will not be published. Required fields are marked *