Getting Empty Set in MySQL on using "not in"-CodePudding

I'm a beginner at MySQL. Due to this, there may be some errors. I have an employee department database for learning purposes. I have stored the supervisorENO for employees who have got a supervisor. If an employee does not have supervisor, their supervisorENO is null.

I have to retrieve the employees' name who is not supervisor. I ran the following command and got Empty set. I was not able to figure it out.

Here is my table:

MariaDB [EMP_DEPT]> select * from EMPLOYEE;
 ----- --------------- --------------------------- --------------- ------------ ------ ------------ ---------- 
| Eno | Ename         | Job_type                  | SupervisorENO | Hire_date  | Dno  | Commission | Salary   |
 ----- --------------- --------------------------- --------------- ------------ ------ ------------ ---------- 
| 111 | Aman Singh    | HR Manager                | NULL          | 2000-01-23 |   50 |       NULL |  5000.00 |
| 112 | Ankesh Kumar  | HR Assistant              | 111           | 2005-10-30 |   50 |       NULL |  4000.00 |
| 113 | Gaurav Singh  | Account Manager           | NULL          | 2002-07-09 |   60 |     100.00 |  6000.00 |
| 114 | Sanjeet Kumar | Accounting Clerk          | 113           | 2015-04-18 |   60 |       NULL |  4500.00 |
| 115 | Rajnish Yadav | Production Manager        | NULL          | 1980-12-04 |   10 |     150.00 |  5500.00 |
| 116 | Sumit Sharan  | Production Incharge       | 115           | 1995-02-24 |   10 |       NULL |  4500.00 |
| 117 | Amartya Sinha | R&D Scientist             | NULL          | 2010-03-15 |   20 |       NULL | 10000.00 |
| 118 | Shahnwaz Khan | R&D Associate Engineer    | 117           | 2016-05-23 |   20 |       NULL |  4000.00 |
| 119 | Sonu Giri     | Purchase Executive        | NULL          | 2013-06-17 |   30 |     140.00 |  7000.00 |
| 120 | Kaushik Kumar | Purchase Specialist       | 119           | 2018-08-13 |   30 |    4500.00 |  4000.00 |
| 121 | Vishal Yadav  | Chief Marketing Officer   | NULL          | 1995-11-19 |   40 |     250.00 | 10000.00 |
| 122 | Satyam Jha    | Digital Marketing Manager | 121           | 2004-09-29 |   40 |       NULL |  4500.00 |
 ----- --------------- --------------------------- --------------- ------------ ------ ------------ ---------- 
12 rows in set (0.001 sec)

MariaDB [EMP_DEPT]> select Ename from EMPLOYEE where Eno not in (select distinct SupervisorENO from EMPLOYEE);
Empty set (0.001 sec)

I am expecting the names of employees with Eno 112, 114, 116, 118, 120, 122 as they are not supervisor. Please help me in figure it out.

CodePudding user response：

select distinct SupervisorENO from EMPLOYEE will also return NULL, any any value when compared to NULL will not actually return True or False, it will return unknown, so to speak. You can read more about that here for example.

So in order to fix your query, you need to exclude NULLs:

select Ename 
  from employee 
 where Eno not in (select distinct SupervisorENO 
                     from employee 
                    where supervisoreno is not null);

Here's a working demo on dbfiddle

CodePudding user response：

You can simply query

select Ename from employee 
where SupervisorEno is not null;

which will return those who have a supervisor, ie. are not themselves a supervisor.

select Ename from employee 
where SupervisorEno is null;

Will return the supervisors.

It is better to avoid sub-queries when not strictly needed.