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

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#
Paparazzi Reply to 2018-02-13 16:47:05Z

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.

Anssssss
4#
Anssssss Reply to 2018-02-13 20:19:22Z

I found the documentation on the behavior: Qualifying Column Names in Subqueries

The general rule is that column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level. If a column does not exist in the table referenced in the FROM clause of a subquery, it is implicitly qualified by the table referenced in the FROM clause of the outer query.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO