1

The table 'employees' from the dataset classicmodels https://www.mysqltutorial.org/mysql-sample-database.aspx has two relevant fields: employeeNumber and reportsTo (direct boss of each employee)

I'm trying to get all employees that are not bosses (they aren't the "reportsTo" of any employee). What I'm trying is:

select employeeNumber from employees
where employeeNumber not in (
    select distinct reportsTo
    from employees
);

But it returns empty set

I don't understand whats happening here. If I invert the condition it works:

select employeeNumber from employees
where employeeNumber in (
    select distinct reportsTo
    from employees
);

This query returns the employees who are bosses of someone. I've already tried using different alias for both tables.

More information

  • Table definition

     CREATE TABLE `employees` (
`employeeNumber` int NOT NULL,
`lastName` varchar(50) NOT NULL,
`firstName` varchar(50) NOT NULL,
`extension` varchar(10) NOT NULL,
`email` varchar(100) NOT NULL,
`officeCode` varchar(10) NOT NULL,
`reportsTo` int DEFAULT NULL COMMENT 'Employees direct superior',
`jobTitle` varchar(50) NOT NULL,
PRIMARY KEY (`employeeNumber`),
KEY `reportsTo` (`reportsTo`),
KEY `officeCode` (`officeCode`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`),
CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)

    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  • Explain enter image description here

ero.rom
  • 43
  • 5
  • Does this answer your question? [NULL values inside NOT IN clause](https://stackoverflow.com/questions/129077/null-values-inside-not-in-clause) However, to address your need: short answer use `NOT EXISTS` instead of `NOT IN` to handle the null situations. This is expected behavior of "NOT IN"; no need to re-invent the wheel here. – xQbert Feb 16 '22 at 14:11
  • Or add to the subquery `select distinct reportsTo from employees where reportsTo is not null` – RiggsFolly Feb 16 '22 at 14:12

0 Answers0