T-SQL [UNION ALL] removes records from query result

Question

Ask a simple UNION ALL query to marry the results of two queries. The first query, executed independently, returns 1208 records and the second 14. I would expect that correctly syntax UNION ALL will return 1222 records, but mine will drop to 896.

Makes me zero meaning:

SELECT a.WBS_ELEMENT_ID as [WBS Element],
a.WBS_ELEMENT_DESC as [WBS Element Desc],
a.UHC_INDUSTRY as [Industry],
a.UHC_SECTOR as [Sector],
a.UHC_DUNS_NUMBER as [UHC DUNS Number],
a.UHC_DUNS_NAME as [UHC DUNS Name],
a.PRIORITY_SUB_SECTOR as [Priority Sub Sector],
a.BUDGET_ALLOCATION as [Budget Allocation],
a.LAST_UPDATED_ON as [Last Updated]
FROM DimSectorPd a
WHERE a.wbs_element_id is not null

UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY a.wbs_element_desc) as [WBS Element],
a.WBS_ELEMENT_DESC as [WBS Element name],
a.UHC_INDUSTRY as [Industry],
a.UHC_SECTOR as [Sector],
a.UHC_DUNS_NUMBER  as [UHC DUNS Number],
a.UHC_DUNS_NAME as [UHC DUNS Name],
a.PRIORITY_SUB_SECTOR as [Priority Sub Sector],
a.BUDGET_ALLOCATION as [Budget Allocation],
a.LAST_UPDATED_ON as [Last Updated]
from dimsectorpd a where a.WBS_ELEMENT_ID is null

Answer 1

Your queries should return all rows in the table. If the table does not change between execution, the results from the execution of the subqueries separately should be the same as when you run them with UNION ALL.

As a note, if you want to simplify the request, you can do:

SELECT COALESCE(a.WBS_ELEMENT_ID,
                ROW_NUMBER() OVER (PARTITION BY wbs_element_id ORDER BY a. wbs_element_desc)
               ) as [WBS Element],
       a.WBS_ELEMENT_DESC as [WBS Element Desc],
       a.UHC_INDUSTRY as [Industry],
       a.UHC_SECTOR as [Sector],
       a.UHC_DUNS_NUMBER as [UHC DUNS Number],
       a.UHC_DUNS_NAME as [UHC DUNS Name],
       a.PRIORITY_SUB_SECTOR as [Priority Sub Sector],
       a.BUDGET_ALLOCATION as [Budget Allocation],
       a.LAST_UPDATED_ON as [Last Updated]
FROM DimSectorPd a;

Answer 2

, , , UNION ALL ROW_NUMBER. :

;WITH q1
AS (
    SELECT a.WBS_ELEMENT_ID AS [WBS Element]
        ,a.WBS_ELEMENT_DESC AS [WBS Element Desc]
        ,a.UHC_INDUSTRY AS [Industry]
        ,a.UHC_SECTOR AS [Sector]
        ,a.UHC_DUNS_NUMBER AS [UHC DUNS Number]
        ,a.UHC_DUNS_NAME AS [UHC DUNS Name]
        ,a.PRIORITY_SUB_SECTOR AS [Priority Sub Sector]
        ,a.BUDGET_ALLOCATION AS [Budget Allocation]
        ,a.LAST_UPDATED_ON AS [Last Updated]
    FROM DimSectorPd a
    WHERE a.wbs_element_id IS NOT NULL

    UNION ALL

    SELECT b.WBS_ELEMENT_ID AS [WBS Element]   --just bring NULL values 
        ,b.WBS_ELEMENT_DESC AS [WBS Element name]
        ,b.UHC_INDUSTRY AS [Industry]
        ,b.UHC_SECTOR AS [Sector]
        ,b.UHC_DUNS_NUMBER AS [UHC DUNS Number]
        ,b.UHC_DUNS_NAME AS [UHC DUNS Name]
        ,b.PRIORITY_SUB_SECTOR AS [Priority Sub Sector]
        ,b.BUDGET_ALLOCATION AS [Budget Allocation]
        ,b.LAST_UPDATED_ON AS [Last Updated]
    FROM dimsectorpd b
    WHERE b.WBS_ELEMENT_ID IS NULL
    )
SELECT CASE 
        WHEN q1.[WBS Element] IS NULL
            THEN ROW_NUMBER() OVER (ORDER BY q1.WBS_Element_Desc)
        ELSE q1.[WBS Element]
        END [WBS_Element]
    ,q1.[WBS Element Desc]
    ,q1.Industry
    ,q1.Sector
    ,q1.[UHC DUNS Number]
    ,q1.[UHC DUNS Name]
    ,q1.[Priority Sub Sector]
    ,q1.[Budget Allocation]
    ,q1.[Last Updated]
FROM q1

Answer 3

Here is a simplified example, can you see if it works on your server?

SELECT a.low AS [My ID], 
    a.name AS [My Letter]
FROM master..spt_values as a
WHERE low is not null

UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY a.name) AS [My ID],
    a.name AS [My Letter]
FROM master..spt_values as a
WHERE a.low is null

master..spt_values ​​as 2515 lines on my system ...