Using LEFT OUTER JOIN query to get zero row counts in SQL
An SQL expert explains how using a LEFT OUTER JOIN query can retrieve zero row counts in SQL.
I'm having a problem with getting a count of rows in different categories using a multiple join statement.
select rs.stratum_id stratum, rs.treatment_group_id treatment_name, count(rs.patient_id) countz from rand_schedule rs, strata s, treatment_groups t where rs.stratum_id = s.stratum_id (+) and rs.treatment_group_id = t.treatment_group_id (+) and rs.status = 'U' group by rs.stratum_id, rs.treatment_group_id order by countz desc;
This query returns the three stratum/treatment combinations that have rows for them, but does not return the one combination that has no rows. I've tried using an NVL on the count but to no avail—it just seems to stop processing once it determines that there are no rows for a category. Is there any way to get it to report the zero row counts?
SELECT rs.stratum_id stratum , rs.treatment_group_id treatment_name , COUNT(rs.patient_id) countz FROM strata s CROSS JOIN treatment_groups t LEFT OUTER JOIN rand_schedule rs ON rs.stratum_id = s.stratum_id AND rs.treatment_group_id = t.treatment_group_id AND rs.status = 'U' GROUP BY rs.stratum_id , rs.treatment_group_id ORDER BY countz DESC;
The CROSS JOIN first obtains all combinations of strata and treatment_groups. Then, for each of these combinations, an attempt is made, using the LEFT OUTER JOIN, to find matching rows of the rand_schedule table. Where no such rows exist, of course, NULLs are returned for that combination. Thus, when the COUNT aggregate function attempts to count them, the NULLs aren't counted, and so the result is a count of zero.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Using the SQL GROUP BY clause for counting combinations
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
How to sort an SQL UNION query with special ORDER BY sequence
SQL expert Rudy Limeback explains how to sort an SQL UNION query using a special ORDERY BY sequence. Continue Reading
Using an SQL SELECT statement from a non-existing table
SQL expert Rudy Limeback explains how to formulate a query using an SQL SELECT statement from a non-existing table. Continue Reading