0

Following are the results of my sql queries:

SELECT DISTINCT(department_id) 
from employees 
ORDER BY department_id;

Result:

DEPARTMENT_ID

       10
       20
       30
       40
       50
       60
       70
       80
       90
      100
      110

Then:

SELECT department_id 
FROM departments 
ORDER BY department_id;

DEPARTMENT_ID

       10
       20
       30
       40
       50
       60
       70
       80
       90
      100
      110
      120
      130
      140
      150
      160
      170
      180
      190
      200
      210
      220
      230
      240
      250
      260
      270

When I execute the Following Query, I get the result:

SELECT department_id 
from departments  
where department_id IN (select distinct(department_id) from employees)  
ORDER BY department_id;

DEPARTMENT_ID

       10
       20
       30
       40
       50
       60
       70
       80
       90
      100
      110

However, the following query returns "NO Rows Selected" when I execute the following query:

SELECT department_id 
from departments  
WHERE department_id NOT IN (select distinct(department_id) from employees)   
ORDER BY department_id;

What I was expected was the Id of departments that are not present in Employees table. I feel its a beginner's mistake however i am not able to resolve this issue. Any help would be largely appreciated.

cdaiga
  • 4,861
  • 3
  • 22
  • 42
Sarun Dahal
  • 377
  • 1
  • 5
  • 17
  • Please show the sample input data (table row values) – OldProgrammer Apr 22 '18 at 18:10
  • 2
    does employee table have any null departmentid that you haven't shown us? – Martin Smith Apr 22 '18 at 18:10
  • 1
    Possible duplicate of [SQL NOT IN not working](https://stackoverflow.com/questions/5231712/sql-not-in-not-working) – Mihai Chelaru Apr 22 '18 at 18:10
  • @Martin Smith Yepp that was indeed the case! I figured that out after reading the answer from Gordon Linoff down below. However, I am still not clear why the null value of department_id of one employees caused the query to produce no result.. Can you help me on this? Thanks in advance – Sarun Dahal Apr 22 '18 at 18:34
  • I tried explaining this here. Not sure how successfully... https://dba.stackexchange.com/questions/48643/query-to-find-guids-not-in-null-containing-subquery-returns-no-results/48657#48657 – Martin Smith Apr 22 '18 at 18:49

1 Answers1

3

This is because at least one department_id in employees isNULL. When any value in theNOT INlist isNULL`, no rows are returned at all.

To fix this, I simply recommend always using NOT EXISTS with a subquery:

SELECT d.department_id 
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE d.department_id = e.department_id)   
ORDER BY d.department_id;

(Or by using a LEFT JOIN/WHERE.)

You could fix this using a WHERE clause in the subquery. I think it is better to use a construct that does what you intend.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786