mysqljoinifnull

Calculate using join between two tables for cases where joined table has no match

I'm trying to calculate values (subtract B from A) between a static and a dynamic table. In some cases the dynamic table won't yet have records matching the static table so I just want it to subtract 0. My Join is returning NULLs in these cases though:

Select T1.A-T2.B from Table1 T1
Left Join Table2 T2
On T1.ID=T2.Table2_ID

How can I set if up so that it just uses 0 then? Not sure if/how to use ifNull here...

Solution

  • Use coalesce() or the MySQL specific ifnull() function to supply a zero if T2.B is not available:

    Select T1.A - coalesce(T2.B,0) as calc
from Table1 T1
Left Join Table2 T2 On T1.ID=T2.Table2_ID