CODEWITHSUNDEEP
sqlsql-server
Beau D'Amore
Beau D'Amore

Reputation: 3392

SQL use joins instead of 'not in'

I am trying to rewrite this query to remove the 'not in' with a left join, but I have been confusing myself. (no surprise)

Original query:

select parentfolderid from folderrelationships with (nolock)
where childfolderid = 14908
and parentfolderid not in (select folderid from folders with (nolock) where typeid in (153,155))

My new version:

select parentfolderid from folderrelationships fr with (nolock)
LEFT JOIN folders f on f.FolderId = fr.ParentFolderID
where childfolderid = 14908 AND f.TypeID in (153,155)

but I am not getting any results so.. I am just not getting it.

I think the problem is where to consider the 'TypeId in (153,155)' in it all. That's confusing me on where to put it.

Upvotes: 1

Views: 75

Answers (2)

Sudeep Devkota
Sudeep Devkota

Reputation: 209

Join is usually an expensive operation in SQL expression; therefore, I must warn you "Think about it before you are trying to convert into left join". But , why left join why not just a simple inner join? Left/Right join produces NULL values for the ones that don't have the matching records. In your case, it looks like you are looking for a matching only cases.

Upvotes: 0

Francisco Goldenstein
Francisco Goldenstein

Reputation: 13767

In order to rewrite the NOT IN as a LEFT JOIN you need to check the LEFT JOIN result is NULL.

select parentfolderid from folderrelationships fr with (nolock)
LEFT JOIN folders f on f.FolderId = fr.ParentFolderID AND f.TypeID in (153,155)
where childfolderid = 14908 AND f.TypeID IS NULL

LEFT JOIN will retrieve rows that fulfill the conditions and others that don't. You only want to keep the ones that don't so you check that you are getting nothing from folders table (f.TypeID IS NULL --> use PK instead of TypeId but I don't know your table structure).

On the other hand, if you are rewriting it because of a performance issue, I don't think you are gonna get it any better doing this.

Upvotes: 3

Related Questions