Why sometimes "NOT IN condition" will return NO results even though condition satisfied?

 Posted by Bandi on 9/10/2013 | Category: Sql Server Interview questions | Views: 1810 | Points: 40
Answer:

SELECT * 

FROM Employees
WHERE DEPARTMENT_ID NOT IN (10, 20, NULL)

Here Department_id is nullable column and I have employees belongs to other departments 30, 40 and so on... Query is expected to return 30, 40 department's employee details... But the above query doesn't return even single row.. WHY?

Reason1: NOT IN operation is equivalent to "AND operation of multiple conditions"........
Condition != Value1 AND Condition != Value1 AND Condition != Value3

Reason2: Any condition checking against a NULL becomes FALSE.
As a result of above two reasons query doesn't return any record


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response