January 29, 2019 at 5:09 am
Hi Guys,
I clearly don't understand how SQL deals with negative selection.
Please consider the following:
create table #a (Colour varchar(20), Size varchar(10))
insert into #a values ('Blue', 'Small'),('Blue', 'Medium'),('Blue', 'Large'),
('Orange', 'Small'),('Orange', 'Medium'),('Orange', 'Large'),
('Red', 'Small'),('Red', 'Medium'),('Red', 'Large')
Now, what I'm trying to do is select all rows except for Orange, Small and Orange, Medium
The following code works by using the NOT operator
select *
from #a
where not (Colour = 'Orange' and (Size = 'Small' or Size = 'Medium'))
However, the following code does not work
select *
from #a
where Colour <> 'Orange' and (Size <> 'Small' or Size <> 'Medium')
How can I achieve this without using NOT? I appreciate that I have a working query, it's just really bugging me why the second query does not return the same results as the first.
January 29, 2019 at 5:23 am
Jim-S - Tuesday, January 29, 2019 5:09 AMHi Guys,I clearly don't understand how SQL deals with negative selection.
Please consider the following:
create table #a (Colour varchar(20), Size varchar(10))insert into #a values ('Blue', 'Small'),('Blue', 'Medium'),('Blue', 'Large'),
('Orange', 'Small'),('Orange', 'Medium'),('Orange', 'Large'),
('Red', 'Small'),('Red', 'Medium'),('Red', 'Large')Now, what I'm trying to do is select all rows except for Orange, Small and Orange, Medium
The following code works by using the NOT operator
select *
from #a
where not (Colour = 'Orange' and (Size = 'Small' or Size = 'Medium'))However, the following code does not work
select *
from #a
where Colour <> 'Orange' and (Size <> 'Small' or Size <> 'Medium')How can I achieve this without using NOT? I appreciate that I have a working query, it's just really bugging me why the second query does not return the same results as the first.
You can transform this using De Morgan's law
not(A and B) = (not A) OR (not B)
So your expression can be rewritten as:select *
from #a
where Colour <> 'Orange' OR (Size <> 'Small' AND Size <> 'Medium')
January 29, 2019 at 5:47 am
Jonathan AC Roberts - Tuesday, January 29, 2019 5:23 AMYou can work this out with De Morgan's law
not(A and B) = (not A) OR (not B)
So your expression can be rewritten as:select *
from #a
where Colour <> 'Orange' OR (Size <> 'Small' AND Size <> 'Medium')
Thank you, makes sense to me now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply