Search code examples
sqlsql-serverexecutionquery-expressions

SQL Server query weird behaviour

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?

Solution

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