Home Understanding an ambiguous column name for inner query

# Understanding an ambiguous column name for inner query

Anssssss
1#
Anssssss Published in 2018-02-13 16:32:19Z
 I ran into a weird query today that I thought would be failed, but it succeeded in an unexpected way. Here's a minimal reproduction of it. Tables and data: CREATE TABLE Employee(ID int, Name varchar(max)) CREATE TABLE Engineer(ID int, Title varchar(max)) GO INSERT INTO Employee(ID, Name) VALUES (1, 'Bobby') INSERT INTO Employee(ID, Name) VALUES (2, 'Sue') INSERT INTO Engineer(ID, Title) VALUES (1, 'Electrical Engineer') INSERT INTO Engineer(ID, Title) VALUES (2, 'Network Engineer')  Queries: --Find all Engineers with same title as Bobby has SELECT * FROM Engineer WHERE Title IN (select Title from Employee WHERE Name = 'Bobby')  This returns all rows in Engineer table (unexpected, I thought it would fail). Note that the above query is incorrect. The inner query uses a column "Title" which doesn't exist in the table being selected from ("Employee"). So it must be binding the Title column value from Engineer in the outer query....which is always equal to itself so all rows are returned I think. I can force it too if I fully qualify the column name, and that would fail as expected: SELECT * FROM Engineer WHERE Title IN (select Empl.Title from Employee Empl WHERE Name = 'Bobby')  This fails with "Invalid column name 'Title'." Apparently if I were to add the Title column to the Employee table, it uses the Employee.Title column value instead. ALTER TABLE Employee ADD Title varchar(max) GO UPDATE Employee SET Title = 'Electrical Engineer' WHERE ID = 1 UPDATE Employee SET Title = 'Network Engineer' WHERE ID = 2 SELECT * FROM Engineer WHERE Title IN (select Title from Employee WHERE Name = 'Bobby')  This returns just one row (as expected). I kind of understand what is happening here, what I'm looking for is a link to some documentation or some keyword that would help me read up and understand it fully (or even some explanation).
Sean Lange
2#
Sean Lange Reply to 2018-02-13 16:37:48Z
 Of course it fails. There is no column named Title in your Employee table. In the query that does work it is a subquery so it is pulling Title from Engineer. You can avoid this entirely if you develop the habit of ALWAYS referencing columns with 2 part naming instead of just the column name. But in your queries you should start learning how to use joins instead of subqueries for everything. Your code would be far less confusing.
Paparazzi
3#
 Since Title is not qualified it uses the Title from table Engineer SELECT * FROM Engineer WHERE Title IN (select Title from Employee WHERE Name = 'Bobby')  In the last it uses the closest Title (from Employee) . If you use alias and 2 part name then you stay out of this confusion. As far as documentation. Finding closest column is probably an undocumented feature.