| Author |
Topic |
|
schifidus
Starting Member
6 Posts |
 Posted - 2011-07-07 : 15:19:18
|
| i've table name transactions with a field named accountID, a table account with a primary key accountID.if i write the next query.."select * from transactions where accountID in (select lostAccountID from accountID)" it returns all records of transactions table...lostAccountID is a field that doesn't exists in account table...infact if i execute "select lostAccountID from accountID" it raises me an error.. how it's possible`??? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-07 : 17:21:56
|
This is because of the way the name resolution rules in SQL works. It first looks for the column in the tables in the inner query (accountID table), and if it does not find it there, it looks in the tables in the outer query - which is likely a column in transactions table. Change your query as shown below and it should give you the same error that you get when you run select lostAccountID from accountIDselect * from transactions where accountID in (select accountID.lostAccountID from accountID) |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-07 : 17:55:13
|
| One reason why you should ALWAYS qualify column names with their table names.I'm going to have to blog this topic. Comes up at least twice a month.--Gail ShawSQL Server MVP |
|
|
|
schifidus
Starting Member
6 Posts |
Posted - 2011-07-08 : 03:05:33
|
| i'm not agreee nested query has a condition where regionId is in (12,13)"select * from transactions where accountID in (select lostAccountID from accountID where regionId is in (12,13))" and regionId is not a field of account or transaction table...first query should search inside the boundary of the nested one.. that are empty... so how can it returns all fields of transaction table? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-08 : 04:07:54
|
| Are you absolutely, 100% sure that neither table has a regionID? If neither really has a regionID, that query will fail with a cannot bind column errorSQL does search first the tables inside the subquery. Since it does not find a table in there that has that column, it then searches the tables in the main query. This is documented, correct behaviour. As for why it returns all rows, that depends on the values on accountID and LostAccountID in the transactions table.--Gail ShawSQL Server MVP |
|
|
|
schifidus
Starting Member
6 Posts |
Posted - 2011-07-08 : 04:13:01
|
| regionId exists but in the account table.. it seems like if it considers column of first table but don't consider where of nested query.. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-08 : 06:11:51
|
This behavior is correct. If it behaved any differently, correlated sub-queries would not work.I don't mean to sound disrespectful when I say this: you are causing yourself a lot of trouble by allowing ambiguity. It is one thing if the objective is to understand the behavior - but if you are trying to get some work done and move on, simply qualify all columns with the table names, and you will have no ambiguity and no confusion.select * from transactions Twhere T.accountID in ( select A.lostAccountID from accountID A where A.regionId in (12,13)) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-08 : 06:17:01
|
quote:
Originally posted by schifidus
regionId exists but in the account table.. it seems like if it considers column of first table but don't consider where of nested query..
It considers both. First SQL will check and see if the column is in a table in the subquery. If it is not then it will check and see if the column is in a table in the outer query. Only if the column is not in any table will an error be thrownThis is correct, normal, documented behaviour. As I said in my first post, this is one of the reasons you need to qualify the column names (tablename.columnname) so that if you do make a mistake like this one (putting a column from an outer table in the subquery instead of a column from the inner table it's obvious what's been done.--Gail ShawSQL Server MVP |
|
|
|
schifidus
Starting Member
6 Posts |
Posted - 2011-07-08 : 08:01:04
|
| I'm sorry i read all Microsoft documentation and it said the it searchs the column in the first table NOT THAT DOESN'T CONSIDER WHERE CONDITION ( where regionId is in (12,13))) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-08 : 08:09:52
|
| Don't shout at us.The point is that the where is doing nothing to filter the subquery, because it's a column in the outer query. It's not that it's ignoring it, it's that it cannot possibly reduce the rows in the subquery in any way, and hence it cannot influence the values returned by the IN.If that's supposed to filter the outer query, put it in the outer query. If it's supposed to do something else, explain what it's supposed to do.--Gail ShawSQL Server MVP |
|
|
|
schifidus
Starting Member
6 Posts |
Posted - 2011-07-08 : 08:21:13
|
| sorry i don't want to shout.. i'm sorry but i tried it with any other DB and the result was or not row in the result or error as i expecetd.. the system can't use column for the first query and doesn't use where condition of nested query.. it's inacceptable and not documented.. |
|
|
|
schifidus
Starting Member
6 Posts |
Posted - 2011-07-08 : 08:24:19
|
| if is as you said.. the system should give me an error regionID is not a column of transaction table.. it can't interpret my column lostAccountID as a column of transaction table but don't consider regionId clause... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-08 : 08:31:56
|
| I honestly don't understand what you're trying to do here. We've explained the scoping rules for queries. You should explicitly qualify your columns and then you won't get surprises.If you need help fixing the query, post the table definitions and explain what the query should do. |
|
|
|
|
Impressive error...