Re: select where not in () fails

>>>>> "Gary" == Gary Stainburn <[email protected]> writes:

 Gary> As I said in my description, some values will be NULL. I just
 Gary> thought that these would not be included in the select. I did not
 Gary> think that it would stop the subselect from working

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_NOT_IN

 Gary> users=# select count(u_id) from users where u_id not in (select
 Gary> distinct emp_u_id from employees where emp_u_id is not null);

Never use DISTINCT inside IN; the IN already implies it.

Always rewrite NOT IN (select ...) to use NOT EXISTS instead, like so:

select count(u_id) from users u
 where not exists (select 1 from employees e where u.u_id=e.emp_u_id);

(and always qualify every column reference in the query, especially when
using IN)

-- 
Andrew (irc:RhodiumToad)