not in - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

not in

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-05-14 : 04:08:13

Hello,
in sql instead of using:
select * from tbl1
where CustID not in (select CustID from tbl2)

Questions:
1- Can I use join query instead of not in ?
2- if so, then is that more efficient than not in ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 04:13:07

1. You can. you need to use left join and filter on NULL

like


SELECT t1.*
FROM tbl1 t1
LEFT JOIN tbl2 t2
On t2.CustID = t1.CustID
WHERE t2.CustID IS NULL

Please keep in mind that if the relationship between tables are not one to one you may get tbl1 data duplicated in output and hence have to use DISTINCT then

2.Depends on factors like table size,indexes presnt etc. Though mostly I've seen NOT EXISTS and JOIN performing better than NOT IN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-05-14 : 05:16:37

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 05:27:17

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-14 : 11:28:04

I found this was helpful: http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources