Answer to question
COALESCE would be utterly pointless where you placed it. The aggregate function
count() never returns
NULL. And logic dictates that
num_60_79 can never be lower than 1 in the subquery. Related:
But that's irrelevant. A subquery returning no row cannot return any values at all.
To be precise, in your case the subquery may return any number of rows but, when joining to
second_table, if there is no row matching on
(assignment_id, num_60_79) then all columns from the subquery
b are filled with
NULL values instead. See:
NATURAL FULL JOIN is a very exotic way to join tables. Especially for somebody still learning how to use
COALESCE. The manual:
NATURAL is shorthand for a
USING list that mentions all columns in
the two tables that have the same names.
FULL OUTER JOIN returns all the joined rows, plus one row for each
unmatched left-hand row (extended with nulls on the right), plus one
row for each unmatched right-hand row (extended with nulls on the left).
I think it's safe to assume there is no column named
second_table, and you surely wouldn't want to involve it in the join conditions if there was one. You only want to join on the column
assignment_id, and that's what you should put in the query instead of the
NATURAL key word.
FULL JOIN is theoretically possible here, we would typically see a
You also don't need two layers of subqueries and some other noise in the query.
All things considered, while you are ...
trying to count the number of columns that is retrieved from the subquery
... my educated guess is you want this query instead:
SELECT s.*, COALESCE(b.num_60_79, 0) AS num_60_79
FROM second_table s
LEFT JOIN (
SELECT assignment_id, count(*) AS num_60_79
WHERE mark_as_percent >= 60
AND mark_as_percent < 80
GROUP BY assignment_id
) b USING (assignment_id);