SQL Server query weird behaviour

Question

I ran into a strange behaviour by a query in SQL Server

I have two tables PrepaidTransactions and BillingData and I am executing the following query

Select * 
from PrepaidTransactions 
where customer_Id in
                  (Select customer_Id 
                   from BillingData 
                   where CommunityId = 10004)

The column customer_Id doesn't belong to table BillingData. Instead of showing error the query is executing and returning all the records from the PrepaidTransactions table

But when I run the following query

Select customer_Id 
from BillingData 
where CommunityId = 10004

it is showing an error

Invalid column name 'customer_Id'.

Can anyone please let me know why the first query is not showing any error?

The first query runs because `customer_Id` unambiguously resolves to `PrepaidTransactions.customer_Id` — ta.speot.is, Mar 15 '14 at 07:51
I am pretty sure the result set is not what you are expecting — Moslem Ben Dhaou, Mar 15 '14 at 08:16
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, Mar 15 '14 at 08:36
@MoslemBenDhaou , yes the result set not what iam expecting. — Mourya, Mar 15 '14 at 09:02
@MartinSmith , yes it is possible duplicate , but before posting this question i checked and couldn't find it. — Mourya, Mar 15 '14 at 09:09

score 3 · Accepted Answer · answered Mar 15 '14 at 07:53

I think these two articles answer your question.

https://connect.microsoft.com/SQLServer/feedback/details/542289/subquery-with-error-does-not-cause-outer-select-to-fail

http://support.microsoft.com/kb/298674

This is expected behavior because your column name is not bound to a table. Therefore, if it can be resolved in the outer table (which in your query's case, it can), then the subquery doesn't fail. If you specify the table BillingData.customer_Id, you will get a failure. The articles say to follow this practice to avoid ambiguity.

the kb article from the second link states that, `In the case of an unqualified column name, the query optimizer assumes that the query is a correlated subquery` , now i understand what is the exact reason and why one should use fully-qualified column name in a sub-query. — Mourya, Mar 15 '14 at 09:08

score 2 · Answer 2 · answered Mar 15 '14 at 07:46

wow! I think that in your first case, customer_Id was being pulled from the outer query. You can test that by doing a table prefix:

Select * from PrepaidTransactions where customer_Id in
(Select PrepaidTransactions.customer_Id from BillingData where CommunityId = 10004)

gets same results, but

Select * from PrepaidTransactions where customer_Id in
(Select BillingData.customer_Id from BillingData where CommunityId = 10004)

I bet that errors?

SQL Server query weird behaviour

2 Answers2