CODEWITHSUNDEEP
sql-server
praveen
praveen

Reputation: 1

Behaviour of IN clause in SQL SERVER

Sample code:

select * 
from users 
where user-id in (select user-id 
                  from accounts 
                  where user-id=@user-id)

I used the above code, and I don't have a user-id column in the accounts table, so instead of showing an error when I execute it, it is returning all the rows present in Users table.

Why is it happening like that. Can any one explain it to me?

Upvotes: 0

Views: 39

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Your question will make sense if you have no column user-id in the table accounts. This will result in user-id being selected from the table users, resulting in all rows being returned.

I imagine you will get an error saying 

Invalid column name 'user-id'

When running this script:

SELECT * 
FROM users 
WHERE 
  [user-id] in (SELECT a.[user-id] FROM accounts a WHERE a.[user-id]=@user-id)

You can read more here (thanks to ughai)

Upvotes: 2

Related Questions