0

i want to know what is the difference between Minus and subquery in the below example. I got 2 different results for both. Basically i am trying to find records that exist in the first but not the 2nd table.

Query 1: 

select count(*) from (
(select OrderID
from DB.Orders)
MINUS
(SELECT OrderID 
  FROM DB.Shipments)) as abc;

Query2:

select count(*) from (
select OrderID
from DB.Orders as a1
where a1.OrderID Not in
(SELECT OrderID 
  FROM DB.Shipments)) as sub;
mucio
  • 7,014
  • 1
  • 21
  • 33
user4611239
  • 1
  • 1

2 Answers2

2

There's two possibilities:

In Query 1, MINUS is implicitly MINUS DISTINCT. Try MINUS ALL.

In Query 2, NOT IN does not behave well with NULL values. Is OrderID allowed to be NULL in DB.Shipments? Try SELECT OrderID FROM DB.Shipments WHERE OrderID IS NOT NULL in your subquery.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
0

This will help you troubleshoot:

select count(*), count(OrderID), count(distinct OrderID) from DB.Orders

Are all three numbers the same?

shawnt00
  • 16,443
  • 3
  • 17
  • 22