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.
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);