Matching multiple columns in a nested query-CodePudding

I'm trying to make a nested query, where I return values from multiple columns and match those with values from another column. Something similar to the code below (this is for work so I can't put the exact code here)

select *
from store1
where (orderNum, customer, total) not in (
select orderNum, customer, total from store2)

When I try to run the code, I get the error:

An expression of non-boolean type specified in a context where a condition is expected, near ','.

Is there a way to do this is SQL Server. I know using a join is an option but I'd prefer to avoid that at this time. Thanks for any help!

CodePudding user response：

SQL Server doesn't support table constructors like that, but there are lots of other ways to do it. My preferred approach is NOT EXISTS:

SELECT * FROM dbo.store1
  WHERE NOT EXISTS 
  (
     SELECT 1 FROM dbo.store2
     WHERE store1.orderNum = store2.orderNum
       AND store1.customer = store2.customer
       AND store1.total = store2.total
  );

For other approaches, see:

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

CodePudding user response：

"count" returns a single value, works fast. Easy to understand.

select *
from store1
where 
(select count(*) from store2 where store2.orderNum=store1.orderNum and store2.customer=store1.customer and store2.total=store1.total)=0