-3

I am new to the SQL, working on this query and found interesting output, please help in understanding why the output is different. Giving literal manager_id values in NOT IN clause (query 1) returns records, but if I use the subquery to return same literal values inside the NOT IN clause (query 2) 0 records are returned.
Thanks in advance

Query 1:

SELECT COUNT(*)
FROM employees
WHERE employee_id NOT IN (
  100, 123, 120, 121, 147, 108, 148, 149, 205, 102, 201, 101, 114, 124, 145, 146, 103, 122
);

Result 1:

COUNT(*)
--------
89

Query 2:

SELECT COUNT(*)
FROM employees
WHERE employee_id NOT IN (
  SELECT DISTINCT manager_id
  FROM employees
);

Result 2:

COUNT(*)
--------
0

Shouldn't it output the same result set?

Alessio Cantarella
  • 5,077
  • 3
  • 27
  • 34
user9403232
  • 1

2 Answers2

0

If you query the same table in an subquery you need to give the tables an alias. This is nessecary, otherwise the columns aren’t unique to identify. It has nothing to do with the not.

Try this:

select count(e.*) 
from employees e 
where EMPLOYEE_ID not in
(select distinct m.manager_id from EMPLOYEES m where m.manager_id is not null);
Nikolaus
  • 1,859
  • 1
  • 10
  • 16
  • in this case in `NOT IN()` it is not nessecary to use a alias or use fully qualified naming.. Only if use a corelated subquery `SELECT * FROM employees_outer ... NOT IN(SELECT ... WHERE employees_inner.id = employees_outer.id)` (as unrelated to question example) then yes then it is nessecary.. – Raymond Nijland Sep 07 '19 at 10:31
  • @RaymondNijland I didn’t know this, but for readability, I always prefer aliasing. – Nikolaus Sep 07 '19 at 10:32
  • *"but for readability"* agree i always use fully qualified naming meself just because the explain shows the alias not the table name when using aliases, makes it more easy to use explain on a query but matter of tasts – Raymond Nijland Sep 07 '19 at 10:33
0

Issue was with subquery returning the null value select DISTINCT(MANAGER_ID) from employees; (null) 100 123 120 121 147 108 148 149 205 102 201 101 114 124 145 146 103 122

user9403232
  • 1