Any equal not in operator in sql server

Posted by Kavi.Sirius under Sql Server on 11/5/2012 | Points: 10 | Views : 1013 | Status : [Member] | Replies : 3
hi ,

any operator in sql server is equal to not in operator

Thanks in advance

Regards
Kavi.n



Responses

Posted by: Saratvaddilli on: 11/5/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
ya we can use Except which is similar to NotIn
EXCEPT operator returns all distinct rows from left hand side table which does not exist in right hand side table. On the other hand "NOT IN" will return all rows from left hand side table which are not present in right hand side table but it will not remove duplicate rows from the result
EXCEPT
Run query with EXCEPT operator, Only distinct rows will return

Select id,name from Dept1
except
Select id,name from Dept2

NOTIN
Run query with NOT IN operator, duplicate rows will exist in the result

Select distinct id,name from Dept1
Where id not in
(Select id from Dept2 )


Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

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

Posted by: Muhsinathk on: 11/6/2012 [Member] Bronze | Points: 25

Up
0
Down
Here is the scenario.
I have a table tbl1 that has a column id. Similary I have a table tbl2 that too had a column id. Now I need to get all the id in tbl1 which does not exist in tbl2.

Soution 1:

Select id from tbl1 where id not in (select id from tbl2)

Solution 2:

Select id from tbl1 a where not exists (select 1 from tbl2 b where a.id = b.id)

Solution 3:

Select a.id from tbl1 a left outer join tbl2 b on a.id = b.id where b.id is null

Solution 4 (SQL Server 2005):

select id from tbl1 except select id from tbl2

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

Posted by: Muhsinathk on: 11/6/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi Kavi,
Please mark as answer if it helpful to you..That helps others who search the same..

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

Login to post response