0

Trying to learn sql queries recently and as the title says I stumbled upon some "behavior" i didn't understand.

This query returns the managers from the database.

SELECT *
FROM employees e
WHERE EXISTS (SELECT * FROM employees f WHERE e.employee_id = f.manager_id)

However if I switch the aliases in the equality it returns all the employees.

SELECT *
FROM employees e
WHERE EXISTS (SELECT * FROM employees f WHERE f.employee_id = e.manager_id)

Isn't employees e and employees f the same table but with different names? If so, why is then the alias order important?

1

Think about the meaning of your queries as well, not just the structure. It is not an order, but an employee-manager (boss) hierarchy.

First query means: list all employees who are managers of other employees.

There are 18 different manager_id values in the table, so you will get the data of 18 employees (as seen on your picture):

SQL> select count(distinct manager_id) from employees;

COUNT(DISTINCTMANAGER_ID)
-------------------------
                       18

Second query means: list all employees that have managers. There are 107 employees and only one of them has no manager, so you get the data of 106 employees (as seen on your picture):

SQL> select count(*), count(manager_id) from employees;

  COUNT(*) COUNT(MANAGER_ID)
---------- -----------------
       107               106

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.