Is this SQL correct?

From: simon <simon_at_whhost.com.au>
Date: Sat, 11 Jun 2005 02:07:28 +0800
Message-ID: <42a9d6e6$1_1@rain.i-cable.com>

Given employee table is :

create table employees (
employee_id number,
manager_id number,
..
)
manager_id is self-referencing another row's employee_id. NULL to represent no manager (e.g. CEO).

To find employees who have at least one person reporting to them, standard answer given in class is:

SELECT employee_id
FROM employees
WHERE employee_id IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL); But why the 'manager_id IS NOT NULL test' in the subquery. I guess even this query works:

SELECT employee_id
FROM employees
WHERE employee_id IN (SELECT manager_id FROM employees );

For the case where the manager_id is null, I think the IN test simply fails, there is no need to exclude it explicitly.

Could anyone confirm my understanding? Received on Fri Jun 10 2005 - 13:07:28 CDT