Re: Is this SQL correct?

On 2005-06-10, simon <simon_at_whhost.com.au> wrote:
> 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?

Yes, your understanding is correct, at least for Oracle where null != X

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/