No error when Sub query not valid

Question

No error when Sub query not valid

tshad

SSCertifiable

Points: 5905
More actions
May 16, 2014 at 3:23 pm

#307469

I have a query that is coming back with all my rows from an "IN" where condition. The problem is that the query in the sub-query is invalid.
I can't recreate it exactly but in the sample here, I don't get any rows back (not sure why they are different) but why am I not getting an error?
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL
DROP TABLE #temp1
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
DROP TABLE #temp2
CREATE TABLE #temp1
(
FirstName varchar(20),
LastName varchar(50)
)
CREATE Table #temp2
(
Name varchar(20)
)
INSERT #temp1 (FirstName,LastName) VALUES ('Tom', 'Johns')
INSERT #temp1 (FirstName,LastName) VALUES ('Larry', 'Smith')
INSERT #temp1 (FirstName,LastName) VALUES ('Greg', 'Franks')
INSERT #temp1 (FirstName,LastName) VALUES ('Sue', 'Marshall')
INSERT #temp2 (Name) VALUES ('Joe Smith')
INSERT #temp2 (Name) VALUES ('Greg Jones')
INSERT #temp2 (Name) VALUES ('Larry Smith')
SELECT *
FROM #temp1 t1
WHERE t1.FirstName IN (SELECT LastName FROM #temp2)
The "SELECT LastName FROM #temp2" subquery is invalid and if you run it by itself you do get the error.
So why no error when this runs.
The other query is:
SELECT *
FROM Staging ves
WHERE ves.ssn IN ( SELECT ssn
FROM Employee )
There is no "ssn" in the Employee table (it is in a different table), I get 12,000 records back from Staging.
Why would that be the case?
Thanks,
Tom

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 1

It's because you're not qualifying where the column is coming from in your IN clause; if you don't qualify the column, and it doesn't exist in the table in the IN clause, SQL Server will auto-resolve the column name to the outer query's table(s) if it can.

For this reason, it's strongly recommended to always qualify your subquery column names, lest a highly-confusing error crop up at some point 🙂

- 😀

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

hisakimatama (5/16/2014)
For this reason, it's strongly recommended to always qualify your subquery column names, lest a highly-confusing error crop up at some point 🙂

+1000

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tshad SSCertifiable Points: 5905 More actions · Answer 3

Interesting and makes sense.

That also answers the 2nd issue. In the case of the 2nd query, ssn is in the staging table and comes back with 12,000 records. But every other field I used, it came back with no records. But I was only changing it in the IN statement but not in the subquery.

This one comes back with no records.

SELECT *

FROM Staging ves

WHERE ves.reg IN ( SELECT ssn

FROM Employee )

But if I put reg in the subquery I get all the records back as well.

That is because I assume it is comparing it with itself. So if the two columns match you get all the records and if they don't match they will get nothing.

Thanks,

Tom