Re: select where not in () fails

>>>>> "Gary" == Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> 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)