What is wrong with this query to get the list of employees not in Dept 1?

SELECT Name
FROM Employee
WHERE DeptID <> 1;

Employee Table:
Id Name DeptID
1 John NULL
2 George 2
3 Smith 1
4 Ray NULL

Answer:

There are 3 Employees (John, George and Ray) not in Dept 1. But Query returns only one result: George.

Since we are just looking for employees not in Dept 1, query does not compare DeptID with NULL. So Employees without a department are not returned.

Correct Query is as follows:

SELECT Name
FROM Employee
WHERE DeptID IS NULL
OR DeptID <> 1;

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 *

*