Need NOT IN Query help [Resolved]

Posted by Sureshij under Sql Server on 9/20/2013 | Points: 10 | Views : 836 | Status : [Member] | Replies : 6
Purpose: list out department names which have NO employees
I have a query as follows:
for example
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT Department_id FROM EMPLOYEES)

But the above query is not working... No records returned even i have some departments with the above condition

ij


Responses

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Very simple thing is you might have NULL values for department_id in EMPLOYEES... so simply add one WHERE condition as follows

SELECT *
FROM departments
WHERE department_id NOT IN (SELECT Department_id FROM EMPLOYEES where department_id is not null )



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
--Altenate is as follows:


SET ANSI_NULLS OFF
GO
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT Department_id FROM EMPLOYEES )


NOTE: the reason behind NO result is that NOT IN operation against ANSI_NULLS ON return FALSE..... That is the reason I changed the ANSI_NULLS mode to OFF

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
This is irrespective of ANSI_NULLS setting

SELECT *
FROM departments
WHERE department_id IN (SELECT department_id FRoM DEPARTMENTS EXCEPT SELECT Department_id FROM EMPLOYEES )


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/20/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Do you know suresh...
Best approach for the above scenario is to use NOT EXISTS clause:
SELECT *

FROM departments d
WHERE NOT EXISTS (SELECT 1 FRoM EMPLOYEES where d.DEpartment_id = DEpartment_id )


NOT IN will be very slow for large set of data.. So better to use NOT EXISTS clause in those scenarios... And this is irrespective of ANSI_NULLS setting in SQL Server

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/25/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi,
"Mark as Answer" if the above posts help you

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sureshij on: 10/1/2013 [Member] Starter | Points: 25

Up
0
Down
Thank You...
All the alternates are looking good..

ij

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response