Re: select where not in () fails

Hi

pá 21. 9. 2018 v 17:08 odesílatel Gary Stainburn <
gary(dot)stainburn(at)ringways(dot)co(dot)uk> napsal:

> 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)
>

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