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

Question

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...

Ifnull is fine, but coalesce conforms to the ansi standard – Strawberry Sep 13 '18 at 23:07 — Strawberry, Sep 13 '18 at 23:07

score 1 · Accepted Answer · answered Sep 13 '18 at 23:28

1

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

answered Sep 13 '18 at 23:28

Paul Maxwell

33,002
3
32
51

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

1 Answers1