Tags
Recently there was a question on one of the MS SQL forum, the query which user had posted was pretty simple but was behaving strangely.
Here I’m demonstrating you all the scenario which user had posted.
Create two tables and inserted few records in both the tables.
DECLARE @Header Table (EmpId Int , FirstName Varchar(50) , LastName Varchar(50)) DECLARE @Detail Table (id Int , Department Varchar(50)) INSERT INTO @Header Values(1 , 'ABC' , 'CBA'), (2 , 'BCD' , 'DCB'),(3 , 'CDE' , 'EDC') INSERT INTO @Detail Values(7 , 'IT'), (8 , 'HR'),(9 , 'Admin')
Now execute below query, which pulls records from Header table for matching EmpId exists in Detail table.
SELECT * FROM @Header WHERE EmpId IN (SELECT EMPID From @Detail)
Following is the result set generated by above query.
Is this query pulling out right data?
Apparently, yes it does, but wait a minute and carefully observe the sub-query.
SELECT EMPID From @Detail
If you carefully check the Detail table definition it doesn’t have any EMPID column defined, infact it has column name ID, but still entire query runs successfully and produces the result.
The EMPID in the sub-query does refer to the outer-query (Header) EMPID column and we are getting the result set.
Alter the above query introduce table alias and run it again.
SELECT * FROM @Header H WHERE H.EmpId IN (SELECT D.EMPID From @Detail D)
Now, the query fails with the error message.
Msg 207, Level 16, State 1, Line 7
Invalid column name ‘EMPID’.
Many a times we do commit this kind of coding errors in our queries without realising it, so it’s always recommended to use table alias’s in your queries to overcome this.
Hope this helps.!!!
