CODEWITHSUNDEEP
sql-server-2008
phpdev76
phpdev76

Reputation: 45

SQL Server results vary using COALESCE and OR

The below queries are generating different counts. it seems to me they are identical.

SELECT 
    COUNT(*)
FROM 
    item1 
RIGHT JOIN 
    (Item2 
INNER JOIN 
    item_master ON Item2.Number = item_master.number) AND (item1.itemId = Item2.itemId)
WHERE  
    COALESCE([Item2].[Amount], item1.Amount, [item_master].[amount], 0 ) > 0

SELECT 
    COUNT(*)
FROM 
    item1 
RIGHT JOIN 
    (Item2 
INNER JOIN 
    item_master ON Item2.Number = item_master.number) AND (item1.itemId = Item2.itemId)
WHERE  
    (Item2.Amount is not NULL and Item2.Amount > 0) OR
    (item1.Amount is not NULL and item1.Amount > 0) OR 
    (item_master.amount is not NULL and item_master.amount > 0)

Upvotes: 0

Views: 48

Answers (2)

will_
will_

Reputation: 5

here's why they're different: coalesce is only going to evaluate against the first non-null value. the 2nd query will accept any of the 3 inputs for evaluation.

take for example the values [-1, 5, null]

  • the 1st query: evaluates to false because -1 <= 0.
  • the 2nd query: the 5 > 0 so it evaluates to true.

if you want to use coalesce in the first query, you'd want to do something like this:

where (coalesce(item2.amount, 0) > 0
or coalesce(item1.amount, 0) > 0
or coalesce(item_master.amount, 0) > 0)

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294367

they're different. COALESCE([Item2].[Amount], item1.Amount, [item_master].[amount], 0 ) > 0 is true if the first non-null value of the three is greater than 0.

Second condition is true if there is any non-null value greater than 0.

Upvotes: 5

Related Questions