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

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

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/