CODEWITHSUNDEEP
sql-servert-sql
Terrance Jackson
Terrance Jackson

Reputation: 1069

Why doesn't AND work in SQL where and IN clause does

If I type 

Select * 
from MyTable 
where LoanNumber = 12345 and LoanNumber = 546787;

this returns nothing.

However if I type 

Select * 
from MyTable
where LoanNumber in (12345, 546787)

it returns both rows. Why is this happening?

Upvotes: 0

Views: 43

Answers (3)

GuidoG
GuidoG

Reputation: 12014

your rows probably look like 

id   loannumber   other columns
--   ----------   -------------
1    12345        etc...
2    546787       more etc...
...

when you write 

Select * 
from MyTable 
where LoanNumber = 12345 and LoanNumber =546787;

you instruct sql server to look for every row like this

for every row in MyTable
  does this row have value 12345 in LoanNumber
  and does this row also have value 546787 in LoanNumber
  If both condtions are true then return this row

Both conditions must be true (because you wrote AND) and that is off course impossible

when you write 

Select * 
from MyTable 
where LoanNumber = 12345 OR LoanNumber =546787;

or 

Select * 
from MyTable 
where LoanNumber in (12345, 546787);

you instruct sql server to look for every row like this

 for every row in MyTable  
   does this row have value 12345 in LoanNumber  
   if not, then does this row have value 546787 in LoanNumber  
   If ONE of the condtions are true then return this row

Only one conditions must be true (because you wrote OR or IN)

As mentioned in comments and other answers, the database will do its comparision on a row basis.

Upvotes: 2

Luis Cazares
Luis Cazares

Reputation: 3585

That's because the evaluation is on a row per row basis. A row can't have 2 different values for a column; it can't be both 12345 and 546787 in the same row.

If you change the AND for an OR, it will return the same results as using IN.

Upvotes: 3

avb
avb

Reputation: 1753

IN means LoanNumber is equal to any of values specified.
Logically it is OR not AND.

Upvotes: 0

Related Questions