convert "not in" to "left join"

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)

convert "not in" to "left join"

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-07-18 : 04:14:44

I have always had the idea that I should use a "left join" instead of a "not in". If you guys/gals tell me that it's not true, we can skip the rest of the question...
But if it is, can you please help me convert the following query using "left join" instead of "not in". I'm not able to get the same result.

select distinct([sc1].[Row])
from [ScreenControl] as [sc1]
where [sc1].[FK_Section_ID] = 2
and [sc1].[RepeatID] =  9
and [sc1].[SubRepeatID] = 0
and [sc1].[Row] not in
(
  select [sc2].[Row]
  from [ScreenControl] as [sc2]
  where [sc2].[FK_Section_ID] = 2
  and [sc2].[RepeatID] =  9
  and [sc2].[SubRepeatID] <> 0
)

I tried with the following, but it gives the opposite result

select distinct([sc1].[Row])
from [ScreenControl] as [sc1]
left join [ScreenControl] as [sc2]
  on [sc2].[Row] = [sc1].[Row]
  and [sc2].[FK_Section_ID] = [sc1].[FK_Section_ID]
  and [sc2].[RepeatID] = [sc1].[RepeatID]
where [sc1].[FK_Section_ID] = 2
and [sc1].[RepeatID] =  9
and [sc1].[SubRepeatID] = 0
and [sc2].[SubRepeatID] <> 0

Thanks in advance
(didn't think the table definition is relevant for this kind of question, but if it is, I'll post it too)

sqllearner05
Starting Member

9 Posts

Posted - 2012-07-18 : 04:59:11

try below one

select [sc1].[Row]
from [ScreenControl] as [sc1] left outer join [ScreenControl] as [sc2]
on [sc1].[Row] = [sc2].[Row]
where [sc2].[Row] is NULL
and [sc1].[FK_Section_ID] = 2
and [sc1].[RepeatID] = 9
and [sc1].[SubRepeatID] = 0
and [sc2].[FK_Section_ID] = 2
and [sc2].[RepeatID] = 9
and [sc2].[SubRepeatID] <> 0

sqllearner

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-07-18 : 05:05:24

Thanks for the reply, but that query doesn't return a result

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-18 : 05:34:23

[code]-- sample table
declare @ScreenControl table (Row int, FK_Section_ID int, RepeatID int, SubRepeatID int)

-- sample data
insert @ScreenControl(Row, FK_Section_ID, RepeatID, SubRepeatID)
select 1,2,9,0 union all
select 1,2,9,1 union all
select 1,2,9,2 union all
select 2,2,9,0 union all
select 3,2,9,0 union all
select 4,2,9,0 union all
select 4,2,9,1 union all
select 5,7,8,0

-- show sample data
select * from @ScreenControl

-- solution using NOT EXISTS()
select row from @ScreenControl s1
where s1.FK_Section_ID = 2
and s1.RepeatID = 9
and s1.SubRepeatID = 0
and not exists(select *
from @ScreenControl s2
where s1.row = s2.row
and s1.FK_Section_ID = s2.FK_Section_ID
and s1.RepeatID = s2.RepeatID
and s1.SubRepeatID <> s2.SubRepeatID)

-- solution using LEFT JOIN
select s1.row
from @ScreenControl s1
left join @ScreenControl s2
on s1.row = s2.row
and s1.FK_Section_ID = s2.FK_Section_ID
and s1.RepeatID = s2.RepeatID
and s1.SubRepeatID <> s2.SubRepeatID
where s1.FK_Section_ID = 2
and s1.RepeatID = 9
and s1.SubRepeatID = 0
AND s2.row IS NULL

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-07-18 : 05:52:02

Thanks Webfred, that is perfect!

I'm still a little confused.
I could get the same result with
"not in"
"except"
"not exists"
"left join"
Which one do I need to use when?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-18 : 06:08:12

Peso wrote a near definitive post on the subject.

My personal feeling is that NOT EXISTS is the most declarative way of doing it (it's the most readable and natural). NOT IN can have a big performance penalty when dealing with NULLABLE fields.....

Read here:
http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-07-18 : 07:25:23

Thanks for the link Charlie.
After reading that blog, I think I'll go with the (NOT) EXISTS.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 10:13:17

NOT IN or NOT EXISTS is not always equivalent to LEFT JOIN. If relationship between tables is not one to one then result of LEFT JOIN wont match NOT IN /NOT EXISTS. so they cant always be substituted one for the other and it really depends on that particular scenario. I do agree to Charlies point on NOT EXISTS vs NOT IN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-18 : 10:19:11

I think a LEFT JOIN and WHERE (<leftJoinedTable.KEY IS NULL>) would be exactly the same results as a NOT EXISTS ( SELECT 1 FROM <theTable> WHERE <OnCondition>)

Even if there is a many relationship....

Left joining just to confirm there is no match is logically equivalent to NOT EXISTING surely....

Obviously a LEFT JOIN can be totally different to an EXISTS clause as you can get many hits. But a LEFT JOIN / NULL check is going to give you only 1 hit exactly the same as the NOT EXISTS....

Can you give an example if I've missed something?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources