My Left Join is NOT working - RESOLVED!!!

Question

Post reply

My Left Join is NOT working - RESOLVED!!!

Jacob Pressures

SSCertifiable

Points: 5804
More actions
June 22, 2020 at 3:29 pm

#3764675
Ok a lead programmer asked me to consider using a query construct that does not include EXCEPT. Rather than use NOT IN, i thought I'd try using a join. However, there is obviously a problem when a subset of the right table should not be excluded.
In the following query, Item has 1200 records. QueueItemDeactivation is completely empty!!! When I uncomment the status line below (the last one), I get no results. Why do i want this line? What I want are all the ItemsIDs in Item excluding all the items in QueueItemDeactivation with the statuses stated below. There are status that I want to included in the results. Statuses that are CMP, for complete or NA, for not applicable. Why do my results disappear when I add the last clause? Is there an alternative way to write this? Thanks!
```
select i.ItemID 
from Item i (nolock) 
left join QueueItemDeactivation qid (nolock) on i.ItemID = qid.ItemID
where i.IsStocking = 1 
and i.IsNonCatalog = 1 
and i.ItemFreezeCodeID <> 4
and qid.Status not in ('HLD', 'INC', 'PND', 'CNL')
```
- This topic was modified 4 years, 6 months ago by Jacob Pressures.
- This topic was modified 4 years, 6 months ago by Jacob Pressures. Reason: This has been resolved

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

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Try moving everything in the WHERE clause to the ON clause.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

First of all, take out those NOLOCK. You may be a gambling man, but gambling does not really have a place in database systems.

The reason you lose the rows is that logically, FROM-JOIN is evaluated first, and this gives you all items, with NULL values for the rows in QueueItemDeactivation where there is no match. Next the WHERE clause is computed, but since you compare NULL to those status values, this means that you lose these rows. That is, you are effectively turning the outer join to an inner join.

Jeff suggest that you move the entire WHERE clause to the ON condition. This solves the problem, but it is clear to only move the condition on QueueItemDeactivation .status.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 3

Try this:

select i.ItemID 
  from Item i (nolock) 
  left join QueueItemDeactivation qid (nolock) 
         on i.ItemID = qid.ItemID
        and qid.Status not in ('HLD', 'INC', 'PND', 'CNL')
 where i.IsStocking = 1 
   and i.IsNonCatalog = 1 
   and i.ItemFreezeCodeID <> 4

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 4

Thanks guys!

I thought about trying that, but most of the time when i try that, I don't see a difference in the result. So I didn't even try. But what i really wanted to learn WHY these things are happening. Erland, walked me through the logic. I saw that the status line was possibly converting the outer join into an inner join--basically saying "It needs something that meets this criteria." but again, i was not certain or clear.

How can i get a better grasp of how the logic works on stuff like this? Is there an article or topic i can look up that will explain how each part effects the query. The execution plan doesn't usually make a lot of sense to me. Am i required to start there?

How do i learn to evaluate these issues logically so i can anticipate the outcome rather than guess at it. (For example, i don't want to get a result but its wrong and don't know it.)

thanks again!!! 😀

Ken McKelvey SSCoach Points: 18859 More actions · Answer 5

You need to understand Logical Query Processing. There are plenty of articles on this. eg:

https://www.sqlservercentral.com/blogs/sql-server-logical-query-processing

FROM; with JOIN, APPLY etc; is always evaluated first.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 6

Jacob Pressures

SSCertifiable

Points: 5804

June 23, 2020 at 3:27 pm

#3764811

Thanks Ken!

Thanks Guys!