Thread: problem with subselect: NOT IN

Hi.

here's my scenario: table employee has emp_id and employee
details. table salesorder has emp_id and sales details.

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);

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);

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

thanks!

-Kevin

__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/?.refer=text

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

Kevin L writes:

> 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);

Are there any NULL values in workorder.emp_id?  If so, you need to exclude
them (... FROM workorder WHERE emp_id IS NOT NULL).

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Kevin L <kevinsl@yahoo.com> writes:
> 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);

Probably you have some NULL values in workorder.  See past discussions
about why NOT IN and NULL don't get along very well (it's not a bug).

            regards, tom lane