Find the employees who have NO dependents using EXCEPT (MINUS)

Question

I'm practicing using MySQL lately and when I studied the EXCEPT (MINUS) operator in MySQL, I encoutered some problem: I have two tables 'employees' and 'dependents'. Tables "employees":

CREATE TABLE employees (
    employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR (20) DEFAULT NULL,
    last_name VARCHAR (25) NOT NULL,
    email VARCHAR (100) NOT NULL,
    phone_number VARCHAR (20) DEFAULT NULL,
    hire_date DATE NOT NULL,
    job_id INT (11) NOT NULL,
    salary DECIMAL (8, 2) NOT NULL,
    manager_id INT (11) DEFAULT NULL,
    department_id INT (11) DEFAULT NULL,
    FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES employees (employee_id));

+Table "dependents":

CREATE TABLE dependents (
    dependent_id INT (11) AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL,
    relationship VARCHAR (25) NOT NULL,
    employee_id INT (11) NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE);

I tried to find the info of employees with no dependents, using the below query:

SELECT e.employee_id, CONCAT(e.first_name, ' ', e.last_name) Employee
FROM employees e
WHERE e.employee_id IN (
    SELECT  e.employee_id  
    FROM employees e
    EXCEPT  
    SELECT  d.employee_id
    FROM dependents d
    ORDER BY employee_id);

The query ran successfully, though, I notice it different from what I expected. When I ran the subquery seperately, I got result of only 12 employees (https://i.stack.imgur.com/KeP9d.png), though, the query above gave out the result of 22 employees (https://i.stack.imgur.com/3Yk6Q.png)]. I also noticed there were some with dependents, which did not meet my requirement. Can someone help me fix the above query, thank you! P/s: I expected my final results to look like this: The expected result

=> With total of 12 employees , which the employee_id follow the figure (https://i.stack.imgur.com/KeP9d.png)

Answer 1

You can use LEFT JOIN with NULL check to achieve what you are looking for :

SELECT e.employee_id, CONCAT(e.first_name, ' ', e.last_name) AS Employee
FROM employees e
LEFT JOIN dependents d ON e.employee_id = d.employee_id
WHERE d.employee_id IS NULL
ORDER BY e.employee_id;