Two table employee and department each table has id and name column. Assume there are numerous content with both field populated and department id reference in each employee table record. Question is How to find all the department names with no related employees in them?
Asked
Active
Viewed 86 times
-1
-
2What have you tried? This is a pretty basic SQL query, which would commonly use `not in`, `not exists`, or `left join`. – Gordon Linoff Oct 24 '17 at 18:19
-
1Please share your schema. – BenM Oct 24 '17 at 18:20
1 Answers
1
SELECT department.id, department.name
FROM department
LEFT OUTER JOIN employee ON employee.department_id = department.id
WHERE employee.id IS NULL;
The left join means that this select will include every department row; where there are no employees for a given department, the employee columns will be null. We can then filter these results to only contain the rows where the employee columns are null.
Andrew Rueckert
- 4,858
- 1
- 33
- 44