June 27, 2020 at 5:29 am
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?
June 27, 2020 at 8:38 am
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]
June 27, 2020 at 1:16 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 29, 2020 at 1:30 am
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!
June 29, 2020 at 7:16 pm
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:
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!
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]
July 2, 2020 at 6:33 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply