Home Understanding an ambiguous column name for inner query
Reply: 0

Understanding an ambiguous column name for inner query

user2055
1#
user2055 Published in May 28, 2018, 3:20 am

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.393021 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO