Re: select where not in () fails

Hi

pá 21. 9. 2018 v 17:08 odesílatel Gary Stainburn <
[email protected]> 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