You have the following rows in the
Customer Table:
CustomerId Status
1 Active
2 Active
3 Inactive
4 NULL
5 Dormant
6 Dormant
You write the following query to return all customers that do not have NULL or ‘Dormant’ for their status:
SELECT * FROM Customer
WHERE Status NOT IN (NULL, ‘Dormant’)
You need to identify the results of the query.
Which result should you expect?
A.
CustomerId Status
B.
CustomerId Status
1 Active
2 Active
3 Inactive
C.
CustomerId Status
1 Active
2 Active
3 Inactive
4 NULL
D.
CustomerId Status
1 Active
2 Active
3 Inactive
4 NULL
5 Dormant
6 Dormant
A
Result : A
Explanation: when NULL is used with NOT IN operator, the condition always evaluates to FALSE and hence no rows are returned
A is the right answer.
B is the right answer if Ansi_nulls is set to OFF
if set ansi_nulls is set to on (default) , then the right answer is A