I don't understand something...

Поиск
Список
Период
Сортировка
От Alexander Pyhalov
Тема I don't understand something...
Дата
Msg-id 4E895728.8000809@rsu.ru
обсуждение исходный текст
Ответы Re: I don't understand something...  (Achilleas Mantzios <achill@matrix.gatewaynet.com>)
Re: I don't understand something...  (Alban Hertroys <haramrae@gmail.com>)
Список 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

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

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: pg_upgrade 8.4 -> 9.1 failures
Следующее
От: Achilleas Mantzios
Дата:
Сообщение: Re: I don't understand something...