SQL Server: difference between "not in" and "not" with "or" condition-CodePudding

Here are two versions of my sample query:

Version 1:

select sum(revenue)
from #sales
where custid not in ('001', '004')

Version 2:

select sum(revenue)
from #sales
where not (custid = '001' or custid = '004')

In table #sales there might be multiple rows per custid. Also, revenue might be NULL.

Both queries yield different results. I do not quite understand why. Can you help?

Thanks and best regards!

CodePudding user response：

As per your query I have created a query in SQL

Create table Sales(id int primary key,custId varchar(10), revenue float null);

insert into Sales values(1,'001',40000);
insert into Sales values(2,'002',10000);
insert into Sales values(3,'003',20000);
insert into Sales values(4,'004',20000);
insert into Sales values(5,'001',50000);
insert into Sales values(6,'001',60000);
insert into Sales values(7,'001',NULL);
insert into Sales values(8,'004',NULL);
insert into Sales values(9,'004',50000);
insert into Sales values(10,'002',50000);

select * from Sales

select sum(revenue)
from Sales
where custid not in ('001', '004')

select sum(revenue)
from Sales
where not (custid = '001' or custid = '004')

I get the same result for both the queries

ID  Custid Revenue
1   001    40000
2   002    10000
3   003    20000
4   004    20000
5   001    50000
6   001    60000
7   001    NULL
8   004    NULL
9   004    50000
10  002    50000

Result of first query

Result of second query

CodePudding user response：

you can using where not to declare multi query condition with multi column. And not in is not. example:

 SELECT *
  FROM [dbo].[DM_NhomTaiSan] where not (ID_NhomTaiSan = 2 or DonViTinh = '')