I don't understand something...

From: Alexander Pyhalov <alp(at)rsu(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: I don't understand something...
Date: 2011-10-03 06:33:12
Message-ID: 4E895728.8000809@rsu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Achilleas Mantzios 2011-10-03 07:19:52 Re: I don't understand something...
Previous Message Peter Eisentraut 2011-10-03 04:12:25 Re: pg_upgrade 8.4 -> 9.1 failures