What will be the result of following query?

Consider following tables:

Employee

ID | Emp_name
1 | Jane
2 | George
3 | John

Department

ID | Dept_name | Emp_id
1 | Marketing | 1
2 | Finance | 2
3 | Technology | null

SELECT *
FROM Employee
WHERE id NOT IN (SELECT Emp_id
FROM Department)

Answer: The above query will return no records. The reason for this is presence of null value in Emp_id column of Department table.

When we do SELECT Emp_id FROM Department, we get null value also. Now in main query we compare NOT IN with null value, then it does not return any result.

The correct query is:

SELECT *
FROM Employee
WHERE id NOT IN (SELECT Emp_id
FROM Department WHERE Emp_id IS NOT NULL)

Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*