Unable to count non-match rows as NULL from JOIN

Question

I have this query joining two "fake" tables:

select count(tb2.col2) from (select unnest(array['A','B']) col1) tb1 left join (select unnest(array['B','C']) col2) tb2 on tb1.col1=tb2.col2 where tb2.col2 IS NULL;

http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/22712

You can see that I expect the count of NULL to be 1 but it shows 0

So how to count the non-match rows?

 col1 | col2
------+------
 A    |
 B    | B

jspcal · Accepted Answer · 2018-05-23 00:50:29Z

2

Use select count(*) instead of select count(tb2.col2).

count(*) is the number of rows.
count(expr) is the number of rows where expr is non-null.

answered May 23, 2018 at 0:50

jspcal

52k7 gold badges76 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

HP. Over a year ago

I wanted to count non-match (aka NULL), not the other way around.

jspcal Over a year ago

But that's what count(*) does. You're already filtering by where tb2.col2 IS NULL.

webmite Over a year ago

@jspcal is correct. the count(*) simply references the row count. Any function applied directly against a 'NULL' column will fail. His approach is correct.

Jamie Over a year ago

The other way to do this is to make a column like CASE WHEN my_column IS NULL THEN 1 ELSE 0 END which can be SUM'd

Collectives™ on Stack Overflow

Unable to count non-match rows as NULL from JOIN

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related