Thread: 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)
Hi
pá 21. 9. 2018 v 17:08 odesílatel Gary Stainburn <gary.stainburn@ringways.co.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
Regards
Pavel
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=#
pá 21. 9. 2018 v 17:20 odesílatel Gary Stainburn <gary.stainburn@ringways.co.uk> napsal:
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
It is common issue. But it has sense.
Regards
Pavel
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)