CODEWITHSUNDEEP
sqlsql-server
snappymcsnap
snappymcsnap

Reputation: 2103

SQL addition with join that can return nulls

Table1 and Table2 both contain columns named Username and Score. Table1 is guaranteed to have all users needed for the query, while Table2 has only a subset of those users. How do I write this query such that I always get the total score of both tables even if Table2 doesn't contain a matching user? In otherwords, if null in Table2 I want to treat it as a zero.

            Table1    Table2
User1       200       100
User2       200       NULL
User3       200       300

User1 TotalScore = 300
User2 TotalScore = 200
User3 TotalScore = 500

Upvotes: 1

Views: 75

Answers (4)

Michał Turczyn
Michał Turczyn

Reputation: 37367

LEFT JOIN will guarantee that you will get all users from Table1. If particular user doesn't exist in Table2, value from this table for the user will be NULL, to treat it as 0, you need to use COALESCE.

Try this:

select t1.username, 
       t1.score,
       t2.score,
       t1.score + coalesce(t2.score, 0) totalScore
from table1 t1
left join table2 t2 on t1.username = t2.username

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use COALESCE for null check and left join with table 2

select username, COALESCE(Table1,0) + COALESCE( Table2,0) from t

so complete query will be

    select t1.username,t1.value as table1_value,
   t2.value as table2_value,
   COALESCE(t1.value,0) + COALESCE( t2.value,0) as TotalScore
   from t1 left join t2 on t1.username =t2.username

Upvotes: 3

XraySensei
XraySensei

Reputation: 192

Use left join for joining and coalesce for merging NULLS into 0.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81970

I think a simple aggregation would do the trick

Select UserName
      ,Score=sum(Score)
 From (
        Select UserName,Score From Table1
        Union All
        Select UserName,Score From Table2
      ) A
 Group By UserName

Upvotes: 4

Related Questions