Обсуждение: select where not in () fails

I have a users table with u_id as primary key.
I have an employee record table with emp_u_id is a foreign key back to the 
users table.

A user may have zero or more employee records (leaves then returns / changes 
department).
An employee may have zero or one user record

The select I am trying to get working to so be able to list all users without 
an employee record. Straight forward right?????

Can anyone see why user record 2212 doesn't appear in the last select 
statement?

users=# select count(u_id) from users;
 count 
-------
   716
(1 row)

users=# select count(emp_u_id) from employees;
 count 
-------
   345
(1 row)

users=# select count(*) from employees;
 count 
-------
   388
(1 row)

users=# select emp_u_id from employees where emp_u_id=2212;
 emp_u_id 
----------
(0 rows)

users=# select u_id from users where u_id=2212;
 u_id 
------
 2212
(1 row)

users=# select count(u_id) from users where u_id in (select distinct emp_u_id 
from employees);
 count 
-------
   323
(1 row)

users=# select count(u_id) from users where u_id not in (select distinct 
emp_u_id from employees);
 count 
-------
     0
(1 row)

On Friday 21 September 2018 16:11:17 Pavel Stehule wrote:
>  maybe some value emp_u_id from employees is NULL. It is expected behave
>
> http://blog.9minutesnooze.com/sql-not-in-subquery-null/
>
> Regards
>
> Pavel


Thanks for this. 

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

users=# select count(u_id) from users where u_id not in (select distinct 
emp_u_id from employees where emp_u_id is not null);
 count 
-------
   393
(1 row)

users=# 

>>>>> "Gary" == Gary Stainburn <gary.stainburn@ringways.co.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)