Odtnhj

I was writing an exercise about "Write a query to find the names (first_name, last_name) of the employees who are not supervisors"

I write it on my own and when i check the result or both, mine has less rows than the other.
I was using the JOIN function and the other doesn't.
I want help to know why two results are so different.

Thanks

the one i use join

SELECT 
first_name, last_name
FROM
employees AS E
 JOIN
departments AS D ON E.department_id = D.department_id
WHERE
NOT EXISTS( SELECT 
 0
 FROM
 departments
 WHERE
 E.manager_id = D.manager_id)
 order by last_name;

the one doesn't use join

SELECT 
b.first_name, b.last_name
FROM
employees b
WHERE
NOT EXISTS( SELECT 
 0
 FROM
 employees a
 WHERE
 a.manager_id = b.employee_id);

The big problem is that the NOT EXISTS subquery is referencing rows from the joined table. The manager_id column is qualified with D., and that's a reference to the joined table, not the table in the FROM clause of the subquery.

 E.manager_id = D.manager_id
 ^^^^^^^ ^

We also suspect that an employee's supervisor is recorded in the employee row, as a reference to another row in the the employee table. But we don't have the schema definition or any example data, so we're just guessing.

It seems like there would be a supervisor_id in the employee table...

SELECT e.first_name
 , e.last_name
 , e.department_id
 , d.department_id
 FROM employees e
 WHERE NOT EXISTS 
 ( SELECT 1 
 FROM employees s 
 WHERE s.id = e.supervisor_id
 ) 
 ORDER
 BY e.last_name
 , e.first_name

It's also possible that some rows in employee have a value in the department_id column that don't have a matching row in the department table. If there is no matching row in department, the inner join will prevent the row from employee from being returned.

We can use an outer join when we want to return rows even when no matching row is found in the joined table. If we want to involve the departments table, because a "supervisor" is defined to be an employee that is the manager of a department, we can employ an anti-join pattern...

SELECT e.first_name
 , e.last_name
 FROM employees e
 LEFT
 JOIN departments d
 ON d.manager_id = e.employee_id 
 WHERE d.manager_id IS NULL 
 ORDER
 BY e.last_name
 , e.first_name

Again, without a schema and some sample data, we're just guessing.

This query can be written using only Employees table, but in your query you have joined the employees table with department table. A query should be written with the minimal amount of tables that suffice your expected output, joining unnecessary tables may result in wrong out puts.

In this case you are joining Employees with department here what if there is no Department_ID in employee table for some employees, so those data will be dropped in the join and result won't be the expected.