SQL LEFT JOIN return 0 rather than NULL

Question

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?

What type of database? SQL Server, MySql, Oracle, ... – Patrick McDonald Jun 24 '09 at 10:56 — Patrick McDonald, Jun 24 '09 at 10:56

score 29 · Accepted Answer · answered Jun 24 '09 at 10:55

29

Use:

ISNULL(count(*), 0)

answered Jun 24 '09 at 10:55

cjk

45,739
9
81
112

10

This would be valid if count(*) returned NULL for no records. However count(*) returns 0. So count(*) and ISNULL(count(*),0) are effectively the same. – Rashmi Pandit Jun 24 '09 at 11:02

score 26 · Answer 2 · edited Nov 20 '12 at 07:10

26

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

edited Nov 20 '12 at 07:10

Firo

30,626
4
55
94

answered Nov 20 '12 at 06:51

kaiyasit phanmakorn

261
3
2

good job bro. I used it by sum() function and it worked. – Hossein Badrnezhad Jul 01 '22 at 12:40

score 16 · Answer 3 · answered Mar 20 '19 at 09:47

16

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.

answered Mar 20 '19 at 09:47

Jasper

404
4
8

instanceof me · Answer 4 · 2009-06-24T13:33:15.090

13

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 ?

edited Jun 24 '09 at 13:33

answered Jun 24 '09 at 10:57

instanceof me

38,520
3
31
40

score 12 · Answer 5 · answered Jun 24 '09 at 11:08

12

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

answered Jun 24 '09 at 11:08

Rashmi Pandit

23,230
17
71
111

That's a good point. In my answer I didn't make any assumptions as to how this count was done. – RichardOD Jun 24 '09 at 11:11

score 7 · Answer 6 · answered Jun 24 '09 at 11:32

7

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.

answered Jun 24 '09 at 11:32

GaZ

2,346
23
46

That's true, but there are usually better ways to abstract out DB difference that by writing non DB specific SQL. – RichardOD Jun 24 '09 at 14:02

RichardOD · Answer 7 · 2009-06-24T11:00:17.597

4

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

edited Jun 24 '09 at 11:00

answered Jun 24 '09 at 10:55

RichardOD

28,883
9
61
81

@Andomar. As Karl hasn't described how he is doing the count (using a count function) or even what database is used, making the assumption that count is the correct answer is wrong. Presumably Karl is not using count as others have correctly pointed out in SQL Server at least this will return 0. – RichardOD Jun 24 '09 at 11:29
For example- Karl could be selecting a column NumberOfItems. If however Karl is using Count, then the question is incorrect. – RichardOD Jun 24 '09 at 11:31

SQL LEFT JOIN return 0 rather than NULL

7 Answers7