Using LEFT JOIN as MINUS or EXCEPT (RESOLVED)

  • Jacob Pressures

    SSCertifiable

    Points: 5804

    The solution is i need to remove the not and add where c.ItemID is null

     

    Ok i'm revisiting this concept again. It is not working. Again, i kinda see why this is not working. But i don't fully understand why it isn't cut and dry.

     

    Why does the following code return all of the values in the left table with null values of the right table. I would expect it to join where it could and then evaluate the rest of the conditions. I would not expect it to create a result set such that there is no join--when the item are indeed in the right table. I could see if they were not int the right table. (Its kinda unexpected that it would create this result set.)

      	create table dbo.Item (
    ItemID int,
    Name varchar(50)
    )

    create table dbo.Collection (
    ItemID int,
    Name varchar(50),
    status varchar(3)
    )

    insert into Item (ItemID, Name)
    values (1, 'Triangle'),
    (2, 'Cirle'),
    (3, 'Square'),
    (4, 'Rectangle'),
    (5, 'Octagon')

    insert into Collection (ItemID, name, Status)
    values (1, 'Triangle', 'HLD'),
    (2, 'Cirle', 'HLD'),
    (3, 'Square', 'HLD'),
    (4, 'Rectangle', 'HLD'),
    (5, 'Octagon', 'HLD')


    select *
    from Item i (nolock)
    left join Collection c (nolock) on i.ItemID = c.ItemID and c.Status not in ('HLD', 'INC', 'PND', 'CNL')

    What i want it to do is an except.

    		select ItemID from item 
    except
    select itemID from collection

    This is not what the above code is doing. Is the only solution to use a subquery NOT IN or NOT EXIST as alternatives?

     

  • Erland Sommarskog

    SSC-Insane

    Points: 23929

    First: stop using NOLOCK all over the place! It litters the code and it can lead to incorrect results being returned in a matter which is difficult to reproduce.

    Next, if you say

    SELECT A LEFT JOIN B

    This means that you want all rows in A to be retained, no matter if there is a match in B or not. That's the essence of a left outer join. You could add a filter to filter out the rows where there are NULLs on the right side and some people write NOT EXISTS that way. Which I always find cumbersome, as I have to read the entire query and follow the logic and eventually understand that "oh, they mean a NOT EXISTS!".

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

  • Jeffrey Williams

    SSC Guru

    Points: 88652

    If what you really want/need is EXCEPT - then put that in a CTE to get the valid list of items, then select from the CTE and join in any other tables.

    with items
    as (
    select itemid from items
    except
    select itemid from collection
    )
    select *
    from items i
    join othertable ot on ot.itemid = i.itemid;

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jacob Pressures

    SSCertifiable

    Points: 5804

    Hi Erland, thanks!

    The (nolock) is out of my control. My code won't pass code review if i don't have it. 😀

    Both tables have the same items in them. So i don't understand exactly why there is a set such that one table is null. If i create a left join, typically it is not going to give me two sets from the left table one that is joined and one that has a set of nulls. I get the basics. I'm just not getting why it returns it with nulls in the other table.  i'm just a tab bit confused.

    Hi Jeffrey,

    My purpose is to use the equivalent of EXCEPT without using EXCEPT. I got the join to work. So that part is resolved.

    Thanks!

     

  • Erland Sommarskog

    SSC-Insane

    Points: 23929

    Jacob Pressures wrote:

    The (nolock) is out of my control. My code won't pass code review if i don't have it. 😀

    If I am doing the code review, it will not pass with NOLOCK. Never!

    You are telling me that you have a standard in your organization that you code dangerously? With NOLOCK, any of this can happen:

    1. You read uncommitted and possibly inconsistent data.
    2. You fail to read data that should have been returned by your query and which had been committed for a long time.
    3. The query explodes with error 601 in the face of the user.

    All three are likely to result in bug reports from dissatisfied users. If they notice. If they think the result they see is correct, and make incorrect business decisions....

    Having a coding standard that requires NOLOCK is so completely irresponsible. If your team thinks that blocking may be a problem, investigate READ COMMITTED SNAPSHOT where readers don't block writers and vice versa. (But where you may read stale data).

    And I am not making things up. Stop this! Now!

    Jacob Pressures wrote:

    Both tables have the same items in them. So i don't understand exactly why there is a set such that one table is null. If i create a left join, typically it is not going to give me two sets from the left table one that is joined and one that has a set of nulls. I get the basics. I'm just not getting why it returns it with nulls in the other table.  i'm just a tab bit confused.

    You have:

    left join Collection c (nolock) on i.ItemID = c.ItemID and c.status not in ('HLD', 'INC', 'PND', 'CNL')

    That is, you are saying that you only want a row in Collection to display if status has a non-null value, and this non-value is any of the other values you have listed.  But in fact all rows has status = 'HLD', which is one of the four values you have singled out.

    It is not clear to me what you really wanted to achieve, so I cannot say what is right for you. But if want to see the values from the Collection, why not remove the condition on status?

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

  • Jacob Pressures

    SSCertifiable

    Points: 5804

    Thanks for the advice Erland! I'm going to look this up.

     

    Having a coding standard that requires NOLOCK is so completely irresponsible. If your team thinks that blocking may be a problem, investigate READ COMMITTED SNAPSHOT where readers don't block writers and vice versa. (But where you may read stale data).And I am not making things up. Stop this! Now!

    Yes my team leads know that (nolock) is a bad idea. I doubt they will change the "standard."

     

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

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