Test by Bikram | Databases | Test 2 | Question: 15

Question

What is the number of rows returned by an SQL query on the below EMP table?

$\begin{array}{|c|c|c|} \hline \text{eno} & \text{ename} & \text{manager} \\ \hline 1 & A & \text{Null} \\ \hline 2 & B & \text{Null} \\ \hline 3 & C & \text{Null} \\ \hline 4 & D & \text{Null} \\ \hline \end{array}$

SELECT * FROM EMP where manager IN (NULL, NULL,NULL);

• A. It will throw an error.
• B. Three rows are selected.
• C. No rows are selected.
• D. One row is selected.

Answer 1

NULL should not be used in NOT IN or IN statements.

Result of an evaluation with NULL is always NULL.

An IN statement will be parsed identically to field=val1 or field=val2 or field=val3.

So putting a null in there will result in field=null......which won't work.

This is why no rows will be returned.

Please refer to the following link for further details.

https://web.archive.org/web/20141229070855/http://www.oraclebin.com/2013/01/beware-of-nulls.html

I found the answer in the following link:

https://stackoverflow.com/questions/6362112/in-clause-with-null-or-is-null/39452788#39452788