select where not in () fails

Поиск
Список
Период
Сортировка
От Gary Stainburn
Тема select where not in () fails
Дата
Msg-id 201809211608.41086.gary.stainburn@ringways.co.uk
обсуждение исходный текст
Ответы Re: select where not in () fails  (Pavel Stehule <pavel.stehule@gmail.com>)
Список pgsql-sql
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)


В списке pgsql-sql по дате отправления:

Предыдущее
От: "Voillequin, Jean-Marc"
Дата:
Сообщение: create policy/using expression
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: select where not in () fails