My Left Join is NOT working - RESOLVED!!!

  • Jacob Pressures

    SSCertifiable

    Points: 5804

    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 2 months, 3 weeks ago by  Jacob Pressures.
    • This topic was modified 2 months, 3 weeks ago by  Jacob Pressures. Reason: This has been resolved
  • Jeff Moden

    SSC Guru

    Points: 997103

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Erland Sommarskog

    SSC-Insane

    Points: 23929

    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: 17334

    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

    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: 18318

    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

    Thanks Ken!

    Thanks Guys!

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

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