Friday, October 28, 2005

SQL Surprise

Got tripped up by this little bit of SQL (which I naively bashed out in Query Analyzer):

select * from Addresses where AddrID in
(select AddrID from BankAccounts where BankID=234255)

The BankAccounts table includes a foreign key to Addresses, giving the address of the branch at which the account is held (there is no intermediate BankBranches table). So I was expecting this query to give me one row from Addresses (or perhaps none). In fact, it gave me the whole damn table.

The problem is that the BankAccounts table doesn't have an AddrID column; it's called BankAddrID. So why didn't the query give an error? I'm no SQL expert but basically it's because the outer select is in scope (which allows correlated subqueries to work) so the query I wrote was effectively:

select AddrID from Addresses
cross join BankAccounts
where BankID=234255

D'oh!

No comments: