Need NOT IN Query help [Resolved]

Posted by Sureshij under Sql Server on 9/20/2013 | Points: 10 | Views : 1415 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	--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

--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

0	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

0	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

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

0	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

0	Thank You... All the alternates are looking good.. ij Sureshij, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts