How do I ignore null values in t-sql function?

Question

I am doing an outer join between two market data tables. The two tables have trading volume reported differently, therefor I need to query them separately and then sum the results.

Issue is that second query is for a trade condition that doesn't happen often and therefore return no result sometimes. So c.volume has value and md.volume is null and I get null as the result of the addition.

How can I make it treat the null as 0?

select 
    c.the_time, c.symbol, c.volume + md.volume 
from 
    -- These are single shares
    (select 
         (time_stamp / 100000) as the_time, symbol, 
         sum(size) as volume 
     from 
         [20160510]
     where 
         price_field = 0
         and (size > 0
         and tradecond != 0)
     group by 
         (time_stamp / 100000), symbol) as c
full outer join 
    (select 
         d.the_time, d.symbol, d.volume as volume 
     from 
         -- These are single shares when multiplied by -1
        (select 
             (time_stamp / 100000) as the_time, symbol, sum(size) * -1 as volume 
         from 
             [20160510]
         where 
             price_field = 0
             and size < 0
         group by 
             (time_stamp / 100000), symbol) as d) as md on md.the_time = c.the_time 
                                                        and md.symbol = c.symbol

Answer 1

you should consider using COALESCE. Note that you can also use ISNULL but COALESCE is ANSI standard function. See reference link.

Your query after using COALESCE will be like

select 
    c.the_time, c.symbol, COALESCE(c.volume,0) + COALESCE(md.volume ,0)
from 
    -- These are single shares
    (select 
         (time_stamp / 100000) as the_time, symbol, 
         sum(size) as volume 
     from 
         [20160510]
     where 
         price_field = 0
         and (size > 0
         and tradecond != 0)
     group by 
         (time_stamp / 100000), symbol) as c
full outer join 
    (select 
         d.the_time, d.symbol, d.volume as volume 
     from 
         -- These are single shares when multiplied by -1
        (select 
             (time_stamp / 100000) as the_time, symbol, sum(size) * -1 as volume 
         from 
             [20160510]
         where 
             price_field = 0
             and size < 0
         group by 
             (time_stamp / 100000), symbol) as d) as md on md.the_time = c.the_time 
                                                        and md.symbol = c.symbol