Any equal not in operator in sql server

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

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

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