Re: problem with subselect: NOT IN

Hi

To start with, I think your queries will be faster if you don't use IN,
but instead used regular joins or EXISTS whenever possible

On Mon, 2 Apr 2001, Kevin L wrote:

> The following works fine: (get all employees who have sold
> something)
>
> SELECT emp_id FROM employee WHERE emp_id IN (SELECT emp_id FROM
> salesorder);

This will probably be faster like this:

SELECT DISTINCT e.emp_id FROM employee e, salesorder s WHERE e.emp_id =
s.emp_id;

Or, probably slower:

SELECT e.emp_id FROM employee e WHERE EXISTS (SELECT 1 FROM salesorder s
WHERE e.emp_id = s.emp_id)

> However, getting employees who have NOT sold something always
> returns zero rows:
>
> SELECT emp_id FROM employee WHERE emp_id NOT IN (SELECT emp_id
> FROM workorder);

Hmm... That should work, but I noticed that in the first query
you use "salesorder" and in the second you use "workorder". Is that where
the fault is?

You might also want to try the following:

SELECT e.emp_id FROM employee e WHERE NOT EXISTS (SELECT 1 FROM salesorder s
WHERE e.emp_id = s.emp_id)

Regards,
Patrik Kudo

> Has anyone encountered this before? I know the second query
> should return something because the data is in the table.
>
> thanks!
>
> -Kevin