How do i replace this left join with NOT IN

Question

Post reply

How do i replace this left join with NOT IN

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
January 23, 2013 at 10:53 am

#288295

Can someone please tell me how to replace the left join with NOT IN. I am doing some testing, i want to see what is performance impact with this change. Rtns returns about 27K records and tbb has 150, i think NOT IN would be more effecient than a left join?
SELECT CONVERT(VARCHAR(4), R.FCde) AS Flty
FROM Rtns R
LEFT JOIN tbB B2
ON Isnull(TAmount, 0) BETWEEN B2.MinCash AND B2.MaxCash
AND B2.BType = 'OtherCash'
WHERE LEFT(R.ID, 1) <> 'C'
AND R.FCde = RIGHT(Db_name(), 4)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Richard Warr SSCertifiable Points: 6966 More actions · Answer 1

A NOT EXISTS may be better than a NOT IN

Something like

SELECT CONVERT(VARCHAR(4), R.FCde) AS Flty

FROM Rtns R

WHERE NOT EXISTS (

SELECT 1 FROM tbB B2

WHERE Isnull(r.TAmount, 0) BETWEEN B2.MinCash AND B2.MaxCash

AND B2.BType = 'OtherCash')

AND LEFT(R.ID, 1) <> 'C'

AND R.FCde = RIGHT(Db_name(), 4)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

These may help:

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 3

Richard Warr (1/23/2013)
A NOT EXISTS may be better than a NOT IN
Something like
SELECT CONVERT(VARCHAR(4), R.FCde) AS Flty
FROM Rtns R
WHERE NOT EXISTS (
SELECT 1 FROM tbB B2
WHERE Isnull(r.TAmount, 0) BETWEEN B2.MinCash AND B2.MaxCash
AND B2.BType = 'OtherCash')
AND LEFT(R.ID, 1) <> 'C'
AND R.FCde = RIGHT(Db_name(), 4)

Do you think the function ISNULL bad on that join? Any thoughts on replacing that?

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

Richard Warr (1/23/2013)
A NOT EXISTS may be better than a NOT IN
Something like
SELECT CONVERT(VARCHAR(4), R.FCde) AS Flty
FROM Rtns R
WHERE NOT EXISTS (
SELECT 1 FROM tbB B2
WHERE Isnull(r.TAmount, 0) BETWEEN B2.MinCash AND B2.MaxCash
AND B2.BType = 'OtherCash')
AND LEFT(R.ID, 1) <> 'C'
AND R.FCde = RIGHT(Db_name(), 4)

Also i am not able to use R.tamount because R is not in scope of the inner query

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sqldba_newbie (1/23/2013)
Richard Warr (1/23/2013)
A NOT EXISTS may be better than a NOT IN
Something like
SELECT CONVERT(VARCHAR(4), R.FCde) AS Flty
FROM Rtns R
WHERE NOT EXISTS (
SELECT 1 FROM tbB B2
WHERE Isnull(r.TAmount, 0) BETWEEN B2.MinCash AND B2.MaxCash
AND B2.BType = 'OtherCash')
AND LEFT(R.ID, 1) <> 'C'
AND R.FCde = RIGHT(Db_name(), 4)
Also i am not able to use R.tamount because R is not in scope of the inner query

Yes it is. That's a standard correlated subquery. Correlated meaning that the inner query references a column in the outer query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass