Feed back NULL value in SQL

Question

When withdraw table has no data [Account No: 100.200.330.444] and deposit table no data [Account No: 100.200.330.444], then only show NULL. But I want to show 0.

I create virtual table and make main balance [Account No: 100.200.330.444]
My Code like deposit_amount - withdraw_amount = Result
When withdraws table empty, then feedback null
I want to show main_balance =0

CREATE VIEW xyz as select account_no as account_no,
 CASE  
  when sum(deposit_amount)=null then 0
  when sum(deposit_amount)=0 then 0
ELSE sum(deposit_amount)
END
-
 (select  
  CASE  
   when sum(withdraw_amount)=null then 0
   when sum(withdraw_amount)=0 then 0
  ELSE sum(withdraw_amount)
 END
 from withdraws
  )as balance from deposits group by account_no

It may be simpler to change the schema so these amount columns are defaulted to `0` rather than NULL — RiggsFolly, Jan 09 '19 at 09:37
Possible duplicate of https://stackoverflow.com/questions/9608639/mysql-comparison-with-null-value — misorude, Jan 09 '19 at 09:37
In SQL we normally use `IS NULL` or `IS NOT NULL` for testing for NULL values. `=` will not correctly test for null as null is considered a missing value — RiggsFolly, Jan 09 '19 at 09:40

Nick · Answer 1 · 2019-01-09T11:53:42.930

I think you should be writing this query as a JOIN instead:

SELECT d.account_no, COALESCE(d.deposits, 0) - COALESCE(w.withdrawals, 0) AS balance
FROM (SELECT account_no, SUM(deposit_amount) AS deposits
      FROM deposits
      GROUP BY account_no) d
LEFT JOIN (SELECT account_no, SUM(withdraw_amount) AS withdrawals
           FROM withdraws
           GROUP BY account_no) w ON w.account_no = d.account_no

Note that I'm assuming that every account has at least one deposit (to open the account). If not, you will need to emulate a FULL JOIN instead (see this question for how to do that).

score 0 · Answer 2 · answered Jan 09 '19 at 09:39

You cannot use arithmetic operators on a Null field. Null is Unknown hence x = unknown is always unknown.

if you want to check nulls you may use

IsNull: i.e. IsNull(Field)
is null: i.e. Where Field is null;
coalesce: i.e. coalesce(Field, valueIfNull)

score 0 · Answer 3 · answered Jan 09 '19 at 12:20

This is almost impossible to express accurately in a MySQL view because of the limits that MySQL places on views. If you want all accounts, then start with the account table and use correlated subqueries in the select:

select a.account_no,
       ((select coalesce(sum(d.deposit_amount), 0)
         from deposits d
         where d.account_no = a.account_no
        ) -
        (select coalesce(sum(d.withdraw_amount), 0)
         from withdraws w
         where w.account_no = a.account_no
        )
       ) as balance
from accounts a;

MySQL does not allow subqueries in the FROM clause, which makes other formulations much more challenging.

Feed back NULL value in SQL

3 Answers3