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 […]