Full Outer Join shows NULL when Group By is used

I have 4 subqueries and each of the subquery is grouped by 'Group name'. The attempt is to have each subquery as a column all grouped by the 'Group Name'. Here is the query:

```
select
coalesce(co.group_name, requests.group_Name, incidents.group_Name, problems.group_Name) as 'SD Groups'
, isnull(co.co, '') as 'CO'
, isnull(incidents.incidents, '' ) as 'Inc'
, isnull(problems.problems, '') as 'Prob'
, isnull(requests.requests, '') as 'Rqst'
from
(
select
groups.last_name AS Group_Name
,count(chg_ref_num) AS 'CO'
from chg
left join ca_contact groups
on chg.group_id = groups.contact_uuid
left join ca_contact assignee
on chg.assignee = assignee.contact_uuid
left join ca_company cc
on assignee.company_uuid = cc.company_uuid
where
groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
, '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
, '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
, '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
, '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')
and status = 'CL'
and convert(varchar, dateadd(hh,-8,dateadd(ss,chg.close_date, '1970')), 101) >= DATEADD(month, datediff(month, 0, getdate()), 0)
and parent is NULL
and cc.company_name = 'XYZ'
group by groups.last_name
) as CO
full outer join
(
select
groups.last_name AS Group_Name
,count(ref_num) AS Requests
from call_req cr
left join ca_contact groups
on cr.group_id = groups.contact_uuid
left join ca_contact assignee
on cr.assignee = assignee.contact_uuid
left join ca_company cc

Is there an obvious flaw in the code? My best estimation is looking at the coalesce function - ...

Yes to both. One problem is that the 2nd (and 3rd) FULL JOIN use the (derived) tables' group_name columns. As a result, the 2nd and 3rd full join cancel the 1st and the 2nd full join respectively. Your query with only the basic structure, stripped from the details:

select  
    coalesce(co.group_name, requests.group_Name, incidents.group_Name, 
             problems.group_Name) as 'SD Groups'
    ---
from
    ( --- ) as CO
full outer join 
    ( --- ) as Requests
    on co.group_name = requests.group_name
full outer join
    ( --- ) as Problems
    on  requests.group_name = problems.group_name
full outer join 
    ( --- ) as Incidents
    on requests.group_name = incidents.group_name

After the 1st full join:

ON co.group_name = requests.group_name

there are possibly rows from either table (groups or requests) that don't match the other and the empty columns are filled with NULL values.

Then the 2nd full join happens:

ON requests.group_name = problems.group_name

but this ON condition will be satisfied only by rows that have non-null requests.group_name. This basically converts these 2 FULL joins to a complicated mess which is almost (but not exactly) the full join of 3 tables. Some rows will not be matched, In particular the rows from tables co and problems that have the same group_name (but no such row exists in table requests) will not be matched but will end up in different rows.

And again the 3rd full join:

ON requests.group_name = incidents.group_name

will make it even more complicated. As a result, your query is equivalent to the the union of (requests left joined to the 3 other tables) and 3 antijoins (each of the 3 tables with requests):

select  
    ---
from
    ( --- ) as Requests
left outer join
    ( --- ) as CO
    on co.group_name = requests.group_name
left outer join
    ( --- ) as Problems
    on  requests.group_name = problems.group_name
left outer join 
    ( --- ) as Incidents
    on requests.group_name = incidents.group_name

union all 

select  
    ---
from
    ( --- ) as CO
left outer join
    ( --- ) as Requests
    on co.group_name = requests.group_name
where 
    requests.group_name is null

union all 

select  
    ---
from
    ( --- ) as Problems
left outer join
    ( --- ) as Requests
    on problems.group_name = requests.group_name
where 
    requests.group_name is null

union all 

select  
    ---
from
    ( --- ) as Incidents
left outer join
    ( --- ) as Requests
    on incidents.group_name = requests.group_name
where 
    requests.group_name is null

That is the reason for the same group_name appearing in two rows. You can see that this happens only when the CO has no rows with such value but the other tables have.

If you want to really have 3 FULL joins, the ON conditions should be rewritten with either ISNULL or COALESCE:

select  
    coalesce(co.group_name, requests.group_Name, incidents.group_Name, 
             problems.group_Name) as [SD Groups]
    ---
from
    ( ---
    ) as CO
full outer join 
    ( ---
    ) as Requests
    on co.group_name = requests.group_name
full outer join
    ( ---
    ) as Problems
    on coalesce(co.group_name, requests.group_name) 
       = problems.group_name
full outer join 
    ( ---
    ) as Incidents
    on coalesce(co.group_name, requests.group_name, problems_group_name) 
       = incidents.group_name
order by 
    --- ;

Other minor issues is the use of 'single quotes' for the aliases. You should either use the standard "double quotes" or the SQL Server's [square brackets]. Or even better don't quote your identifiers and keep them without spaces and other weird characters. Single quoted strings should be used only for string literals, not for identifiers.

StackExchange Database Administrators Q#140876, answer score: 6