[GENERAL] I don't understand something...

2011-10-03 Thread Alexander Pyhalov 

Hello.
I was asked a simple question. We have table employees:
 \d employees
Table public.employees
 Column |Type | 
Modifiers

+-+-
 employee_id| integer | not null default 
nextval('employees_employee_id_seq'::regclass)

 first_name | character varying(20)   |
 last_name  | character varying(25)   | not null
 email  | character varying(25)   | not null
 phone_number   | character varying(20)   |
 hire_date  | timestamp without time zone | not null
 job_id | character varying(10)   | not null
 salary | numeric(8,2)|
 commission_pct | numeric(2,2)|
 manager_id | integer |
 department_id  | integer |
Indexes:
employees_pkey PRIMARY KEY, btree (employee_id)
emp_email_uk UNIQUE, btree (email)
emp_department_ix btree (department_id)
emp_job_ix btree (job_id)
emp_manager_ix btree (manager_id)
emp_name_ix btree (last_name, first_name)
Check constraints:
emp_salary_min CHECK (salary  0::numeric)
Foreign-key constraints:
employees_department_id_fkey FOREIGN KEY (department_id) 
REFERENCES departments(department_id)

employees_job_id_fkey FOREIGN KEY (job_id) REFERENCES jobs(job_id)
employees_manager_id_fkey FOREIGN KEY (manager_id) REFERENCES 
employees(employee_id)

Referenced by:
TABLE departments CONSTRAINT dept_mgr_fk FOREIGN KEY 
(manager_id) REFERENCES employees(employee_id)
TABLE employees CONSTRAINT employees_manager_id_fkey FOREIGN 
KEY (manager_id) REFERENCES employees(employee_id)
TABLE job_history CONSTRAINT job_history_employee_id_fkey 
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)


Now we want to select count of all employees who doesn't have any 
subordinates (query 1):
 SELECT count(employee_id) from employees o where not exists  (select 1 
from employees  where manager_id=o.employee_id);

 count
---
89
(1 row)

We can select count of all managers (query 2):
SELECT count(employee_id) from employees where employee_id  in (select 
manager_id from employees);

 count
---
18
(1 row)

But if we reformulate the first query in the same way, answer is 
different (query 3):
SELECT count(employee_id) from employees where employee_id not in 
(select manager_id from employees) (query 3);

 count
---
 0
(1 row)

I don't understand why queries 1 and 3 give different results. They 
seems to be the same... Could someone explain the difference?


--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of Southern Federal University

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] I don't understand something...

2011-10-03 Thread Achilleas Mantzios 
Alexander, that's a classic one,
rewrite your last query as :

SELECT count(employee_id) from employees where employee_id not in 
(select manager_id from employees WHERE manager_id  IS NOT NULL);

NULLS semantics are sometimes not so obvious.

Στις Monday 03 October 2011 09:33:12 ο/η Alexander Pyhalov έγραψε:
 Hello.
 I was asked a simple question. We have table employees:
   \d employees
  Table public.employees
   Column |Type | 
  Modifiers
 +-+-
   employee_id| integer | not null default 
 nextval('employees_employee_id_seq'::regclass)
   first_name | character varying(20)   |
   last_name  | character varying(25)   | not null
   email  | character varying(25)   | not null
   phone_number   | character varying(20)   |
   hire_date  | timestamp without time zone | not null
   job_id | character varying(10)   | not null
   salary | numeric(8,2)|
   commission_pct | numeric(2,2)|
   manager_id | integer |
   department_id  | integer |
 Indexes:
  employees_pkey PRIMARY KEY, btree (employee_id)
  emp_email_uk UNIQUE, btree (email)
  emp_department_ix btree (department_id)
  emp_job_ix btree (job_id)
  emp_manager_ix btree (manager_id)
  emp_name_ix btree (last_name, first_name)
 Check constraints:
  emp_salary_min CHECK (salary  0::numeric)
 Foreign-key constraints:
  employees_department_id_fkey FOREIGN KEY (department_id) 
 REFERENCES departments(department_id)
  employees_job_id_fkey FOREIGN KEY (job_id) REFERENCES jobs(job_id)
  employees_manager_id_fkey FOREIGN KEY (manager_id) REFERENCES 
 employees(employee_id)
 Referenced by:
  TABLE departments CONSTRAINT dept_mgr_fk FOREIGN KEY 
 (manager_id) REFERENCES employees(employee_id)
  TABLE employees CONSTRAINT employees_manager_id_fkey FOREIGN 
 KEY (manager_id) REFERENCES employees(employee_id)
  TABLE job_history CONSTRAINT job_history_employee_id_fkey 
 FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
 
 Now we want to select count of all employees who doesn't have any 
 subordinates (query 1):
   SELECT count(employee_id) from employees o where not exists  (select 1 
 from employees  where manager_id=o.employee_id);
   count
 ---
  89
 (1 row)
 
 We can select count of all managers (query 2):
 SELECT count(employee_id) from employees where employee_id  in (select 
 manager_id from employees);
   count
 ---
  18
 (1 row)
 
 But if we reformulate the first query in the same way, answer is 
 different (query 3):
 SELECT count(employee_id) from employees where employee_id not in 
 (select manager_id from employees) (query 3);
   count
 ---
   0
 (1 row)
 
 I don't understand why queries 1 and 3 give different results. They 
 seems to be the same... Could someone explain the difference?
 
 -- 
 Best regards,
 Alexander Pyhalov,
 system administrator of Computer Center of Southern Federal University
 



-- 
Achilleas Mantzios

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] I don't understand something...

2011-10-03 Thread Alexander Pyhalov 

On 10/03/2011 11:19, Achilleas Mantzios wrote:

Alexander, that's a classic one,
rewrite your last query as :

SELECT count(employee_id) from employees where employee_id not in
(select manager_id from employees WHERE manager_id  IS NOT NULL);

NULLS semantics are sometimes not so obvious.


Thanks.
It's confusing, that IN check works as expected, but NOT IN works this 
way...


--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of Southern Federal University

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] I don't understand something...

2011-10-03 Thread hubert depesz lubaczewski 
On Mon, Oct 03, 2011 at 11:48:45AM +0400, Alexander Pyhalov wrote:
 On 10/03/2011 11:19, Achilleas Mantzios wrote:
 Alexander, that's a classic one,
 rewrite your last query as :
 
 SELECT count(employee_id) from employees where employee_id not in
  (select manager_id from employees WHERE manager_id  IS NOT NULL);
 
 NULLS semantics are sometimes not so obvious.
 
 Thanks.
 It's confusing, that IN check works as expected, but NOT IN works
 this way...

If I might plug:
http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] I don't understand something...

2011-10-03 Thread Alban Hertroys 
On 3 October 2011 08:33, Alexander Pyhalov a...@rsu.ru wrote:
 Now we want to select count of all employees who doesn't have any
 subordinates (query 1):
  SELECT count(employee_id) from employees o where not exists  (select 1 from
 employees  where manager_id=o.employee_id);
  count
 ---
    89
 (1 row)

 We can select count of all managers (query 2):
 SELECT count(employee_id) from employees where employee_id  in (select
 manager_id from employees);
  count
 ---
    18
 (1 row)

 But if we reformulate the first query in the same way, answer is different
 (query 3):
 SELECT count(employee_id) from employees where employee_id not in (select
 manager_id from employees) (query 3);
  count
 ---
     0
 (1 row)

 I don't understand why queries 1 and 3 give different results. They seems to
 be the same... Could someone explain the difference?

That's because NOT IN returns NULL if there are any NULLs in the list.
As the WHERE-clause requires something to evaluate to either true or
false (NULL won't do), you (correctly) get false if someone is a
manager, but also if _anyone_ is NOT a manager.

That's an artefact of how 3-valued logic is implemented in the SQL standard.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general