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
