Correlated Sub-Query Should Throw Exception

Question

Sorry for the bad title - I can't think of a better one.

Essentially, SQL Server 2008 is processing a sub-query that I would have expected to fail.

Here's a simple example:

CREATE TABLE UserDemographics
(
    UserID      INT,
    UserName    NVARCHAR(500)
)

CREATE TABLE UserAddresses
(
    UserAddressID   INT,
    Address     NVARCHAR(500)
)


INSERT INTO UserDemographics(UserID, UserName) VALUES(1, 'Joe'), (2, 'Sam'), (3, 'Ed')

INSERT INTO UserAddresses(UserAddressID, Address) VALUES(1, '1st Street'), (2, 'Old Address 1'), (3, 'Old Address 2')

DELETE FROM UserDemographics
WHERE UserID IN
(
    SELECT UserID FROM UserAddresses
)

Here is why this is interesting: UserAddresses does not have a column called UserID. The correlated sub-query is selecting a column that does not exist in the table it is querying.

Obviously, it has something to do with the correlated-sub-query - UserID is a column in the UserDemographics table, and for some reason the database is making that reference.

Perhaps the UserDemographics table is implied in the from-list in the sub-query.

Can anyone explain this?

possible duplicate of [sql server 2008 management studio not checking the syntax of my query](http://stackoverflow.com/questions/4594733/sql-server-2008-management-studio-not-checking-the-syntax-of-my-query) — Martin Smith, May 15 '12 at 05:17

score 6 · Accepted Answer · answered May 15 '12 at 00:42

Yes. The correlated subquery is taking UserDemographics.Userid because that is the only UserId in scope.

Imagine that you had AddressId in the demographics table and did:

DELETE FROM UserDemographics
WHERE AddressID IN ( SELECT AddressId FROM UserAddresses )

In this case, AddressId in the subquery would come from UserAddresses.

The moral of this story is: Always to use table names or aliases when referring to columns in subqueries. The following gets the error you expect:

DELETE FROM UserDemographics
WHERE UserID IN ( SELECT ua.UserId FROM UserAddresses ua)

Yup - that's a good rule. I usually alias pretty much everything, but this was a quick script to do a cleanup, and it cleaned up more than expected. — David Griffiths, May 15 '12 at 16:45

score 0 · Answer 2 · answered May 15 '12 at 08:24

UserAddressID of table UserAddresses is actually a foreign key to the UserID of tableUserDemographics

because of the values you presented

so you should use UserAddressID

DELETE FROM UserDemographics
WHERE UserID IN
(
    SELECT UserAddressID FROM UserAddresses
)

but i mean this script will delete all users that have addresses in UserAddresses table

in this case you should add a where clause

DELETE FROM UserDemographics
WHERE UserID IN
(
    SELECT UserAddressID FROM UserAddresses where ???
)

unless your intention is to delete any user who has a reference in UserAddresses table

Correlated Sub-Query Should Throw Exception

2 Answers2