agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedselect where not in () fails
5+ messages / 3 participants
[nested] [flat]
* select where not in () fails
@ 2018-09-21 15:08 Gary Stainburn <gary.stainburn@ringways.co.uk>
2018-09-21 15:11 ` Re: select where not in () fails Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gary Stainburn @ 2018-09-21 15:08 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select where not in () fails
2018-09-21 15:08 select where not in () fails Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2018-09-21 15:11 ` Pavel Stehule <pavel.stehule@gmail.com>
2018-09-21 15:20 ` Re: select where not in () fails Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2018-09-21 15:11 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql@lists.postgresql.org
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
http://blog.9minutesnooze.com/sql-not-in-subquery-null/
Regards
Pavel
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select where not in () fails
2018-09-21 15:08 select where not in () fails Gary Stainburn <gary.stainburn@ringways.co.uk>
2018-09-21 15:11 ` Re: select where not in () fails Pavel Stehule <pavel.stehule@gmail.com>
@ 2018-09-21 15:20 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2018-09-21 16:14 ` Re: select where not in () fails Pavel Stehule <pavel.stehule@gmail.com>
2018-09-22 18:13 ` Re: select where not in () fails Andrew Gierth <andrew@tao11.riddles.org.uk>
0 siblings, 2 replies; 5+ messages in thread
From: Gary Stainburn @ 2018-09-21 15:20 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org; +Cc: Pavel Stehule <pavel.stehule@gmail.com>
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=#
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select where not in () fails
2018-09-21 15:08 select where not in () fails Gary Stainburn <gary.stainburn@ringways.co.uk>
2018-09-21 15:11 ` Re: select where not in () fails Pavel Stehule <pavel.stehule@gmail.com>
2018-09-21 15:20 ` Re: select where not in () fails Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2018-09-21 16:14 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2018-09-21 16:14 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql@lists.postgresql.org
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=#
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select where not in () fails
2018-09-21 15:08 select where not in () fails Gary Stainburn <gary.stainburn@ringways.co.uk>
2018-09-21 15:11 ` Re: select where not in () fails Pavel Stehule <pavel.stehule@gmail.com>
2018-09-21 15:20 ` Re: select where not in () fails Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2018-09-22 18:13 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Andrew Gierth @ 2018-09-22 18:13 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql@lists.postgresql.org, Pavel Stehule <pavel.stehule@gmail.com>
>>>>> "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)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-09-22 18:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-21 15:08 select where not in () fails Gary Stainburn <gary.stainburn@ringways.co.uk>
2018-09-21 15:11 ` Pavel Stehule <pavel.stehule@gmail.com>
2018-09-21 15:20 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2018-09-21 16:14 ` Pavel Stehule <pavel.stehule@gmail.com>
2018-09-22 18:13 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox