SELECT values of a column that are not in another column in the same table MySQL-CodePudding

I have a sample table called "Quant1" as follows (ID - int PK, Name - Varchar(45), Salary - float, manager_id - int):

ID	Name	Salary	Manager_id
1	John	300	3
2	Mike	200	3
3	Sally	550	4
4	Jane	500	7
5	Joe	600	7
6	Dan	600	3
7	Phil	550	NULL

I want to list employees not managing anyone, i.e. ID 1,2,5,6.So I write the following code:

SELECT *
FROM quant1
WHERE id NOT IN 
    (SELECT DISTINCT manager_id
    FROM quant1);

But it doesn't result in the desired outcome. Can anyone spot the error in my script? Thanks in advance!

CodePudding user response：

You have to exclude the NULL manager ID from the subquery, because all comparisons with NULL fail.

SELECT *
FROM quant1
WHERE id NOT IN 
    (SELECT DISTINCT manager_id
    FROM quant1
    WHERE manager_id IS NOT NULL);

DEMO

CodePudding user response：

This query will return all rows from the table_name table where the column_name column is not contained in the column_name column from the table_name table.

SELECT * FROM table_name WHERE column_name NOT IN (SELECT column_name FROM table_name)

CodePudding user response：

SELECT *
FROM quant1
WHERE id NOT IN 
    (SELECT DISTINCT manager_id
    FROM quant1 WHERE manager_id IS NOT NULL);