sql select - another select with NULL returns only NULL

Question

I have a like the one below that does a minus from another select. The problem I have is that if the second SELECT (the one to do minus with) returns NULL, the full query returns NULL even if the first query has values. Seems like MySQL thinks 1-NULL=NULL. How can I fix this?

SELECT round(sum(iv.`amount`)) - 
(
 SELECT round(sum(pay.`amount`)) amountSum

     FROM invoice iv
    LEFT JOIN invoiceFactoring ivf on ivf.invoiceID=iv.invoiceID 
    LEFT JOIN user systemuser ON (systemuser.userID=iv.ownerUserID)
    LEFT JOIN Payment pay ON (pay.`invoiceID`=iv.`invoiceID`)
     WHERE 
        (iv.invoiceStateID = 2 OR iv.invoiceStateID = 3) 
     AND 
    (ivf.`invoiceFactoringProcessID` = 7)
     AND     (pay.`paymentMethodID` = 1 OR pay.`paymentMethodID` = 2)
     AND systemuser.`groupID` = 1 
     AND iv.`disabled` <> 1 
     AND ivf.`invoiceExpiryDate` BETWEEN date_add(now(), INTERVAL - 28 DAY) AND date_add(now(), INTERVAL - 21 DAY)

)
    FROM invoice iv
    LEFT JOIN invoiceFactoring ivf on ivf.invoiceID=iv.invoiceID 
        LEFT JOIN user systemuser ON (systemuser.userID=iv.ownerUserID)
    WHERE 
    (iv.invoiceStateID = 2 OR iv.invoiceStateID = 3)
        AND 
    (ivf.`invoiceFactoringProcessID` = 7 or ivf.`invoiceFactoringProcessID`)
    AND systemuser.`groupID` = 1 
    AND iv.`disabled` <> 1   /* ta bort de som är inaktiva*/    
    AND ivf.`invoiceExpiryDate` BETWEEN date_add(now(), INTERVAL - 28 DAY) AND date_add(now(), INTERVAL - 21 DAY)

That's not just MySQL - That's not even just SQL - That's how NULL is defined to behave - period. — MatBailie
– MatBailie, Commented Feb 3, 2012 at 13:45
@Dems: see how 1-0=0 can be considered a universal truth but I don't think the same can be said of 1-NULL=NULL. Rather, it is something defined** by the SQL Standard and implemented by mySQL. ** I mean that loosely! — onedaywhen
– onedaywhen, Commented Feb 3, 2012 at 14:52
@onedaywhen - Except that NULL isn't a purely SQL concept. It's since transcended that. — MatBailie
– MatBailie, Commented Feb 3, 2012 at 15:19

slapthelownote · Accepted Answer · 2012-02-03 13:25:20Z

perhaps you can wrap the "inner" sql with an IFNULL

SELECT round(sum(iv.`amount`)) - 
IFNULL((
 SELECT round(sum(pay.`amount`)) amountSum
 FROM invoice iv
 LEFT JOIN invoiceFactoring ivf on ivf.invoiceID=iv.invoiceID 
 LEFT JOIN user systemuser ON (systemuser.userID=iv.ownerUserID)
 LEFT JOIN Payment pay ON (pay.`invoiceID`=iv.`invoiceID`)
 WHERE 
    (iv.invoiceStateID = 2 OR iv.invoiceStateID = 3) 
 AND 
    (ivf.`invoiceFactoringProcessID` = 7)
 AND     (pay.`paymentMethodID` = 1 OR pay.`paymentMethodID` = 2)
 AND systemuser.`groupID` = 1 
 AND iv.`disabled` <> 1 
 AND ivf.`invoiceExpiryDate` BETWEEN date_add(now(), INTERVAL - 28 DAY) 
 AND date_add(now(), INTERVAL - 21 DAY)
),0)

FROM invoice iv
LEFT JOIN invoiceFactoring ivf on ivf.invoiceID=iv.invoiceID 
    LEFT JOIN user systemuser ON (systemuser.userID=iv.ownerUserID)
WHERE 
(iv.invoiceStateID = 2 OR iv.invoiceStateID = 3)
    AND 
(ivf.`invoiceFactoringProcessID` = 7 or ivf.`invoiceFactoringProcessID`)
AND systemuser.`groupID` = 1 
AND iv.`disabled` <> 1   /* ta bort de som är inaktiva*/    
AND ivf.`invoiceExpiryDate` BETWEEN date_add(now(), INTERVAL - 28 DAY) AND date_add(now(), INTERVAL - 21 DAY)

oharab · Accepted Answer · 2012-02-03 13:24:23Z

0

Doing anything with a null returns null. You need to define what you want to happen if it is null using COALESCE or IFNULL. Check http://www.w3schools.com/sql/sql_isnull.asp for an example.

answered Feb 3, 2012 at 13:24

oharab

4,4253 gold badges22 silver badges15 bronze badges

1 Comment

onedaywhen Over a year ago

"Doing anything with a null returns null" -- NULL IS NULL evaluates to TRUE.

ruakh · Accepted Answer · 2012-02-03 13:24:30Z

0

Seems like MySQL thinks 1-NULL=NULL.

Yes; and it is correct. "One" minus "unknown" is "unknown". If you want the second subquery's NULL to be treated as 0, then you need to wrap it in a call to the COALESCE operator: change (SELECT ...) to COALESCE((SELECT ...), 0).

answered Feb 3, 2012 at 13:24

ruakh

185k29 gold badges292 silver badges324 bronze badges

Collectives™ on Stack Overflow

sql select - another select with NULL returns only NULL

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related