CODEWITHSUNDEEP
sqlnullleft-join
Karl
Karl

Reputation: 1317

SQL LEFT JOIN return 0 rather than NULL

I want to join two tables, with the number of records for each type being counted. If there are no records of that type in the left table I want a 0 to be returned, not a null.

How can I do this?

Upvotes: 48

Views: 97329

Answers (7)

Jasper
Jasper

Reputation: 434

COALESCE(XXX, 0)

E.g.

SELECT branch1_id, branch1_name, COALESCE(devnum, 0) FROM
    branch1 as S LEFT JOIN view_inner_zj_devnum as B ON S.branch1_id = B.bid1 GROUP BY branch1_id;

That works for me.

Upvotes: 19

kaiyasit phanmakorn
kaiyasit phanmakorn

Reputation: 261

You can use "CASE"

SELECT T1.NAME, CASE WHEN T2.DATA IS NULL THEN 0 ELSE T2.DATA END
FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID

Upvotes: 26

instanceof me
instanceof me

Reputation: 39138

ISNULL(nullable, value_if_null) for MsSQL, COALESCE(nullable1, nullable2, ..., value_if_null) for MySQL.

Edit: As I'm told, COALESCE works for both, so I'd choose that to replace NULL columns.

Now I think that COUNT()ing NULL values returns 0 in MySQL too, so I agree with Rashmi. Could you show us the query and the wanted result ?

Upvotes: 15

GaZ
GaZ

Reputation: 2406

COALESCE is more cross-compatible than ISNULL or NVL (it works on MSSQL, Oracle, MySQL, Derby, et al.). But I am not sure about the performance differences.

Upvotes: 7

Rashmi Pandit
Rashmi Pandit

Reputation: 23818

I am not sure if I have understood your exact problem, but in sqlserver on a left join, you will get a count as 0 if your query is something like this: 

select t1.id, count(t2.id)
from table1 t1
left outer join table2 t2
on t1.id = t2.id
group by t1.id

Upvotes: 13

RichardOD
RichardOD

Reputation: 29157

Look at IsNull in SQL Server and Sybase. Use NVL in Oracle.

Upvotes: 4

cjk
cjk

Reputation: 46425

Use:

ISNULL(count(*), 0)

Upvotes: 29

Related Questions