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

Question

Have a simple UNION ALL query marrying the results of two queries. The first query, run independently, returns 1208 records and the second 14. I would expect a properly syntaxed UNION ALL to return 1222 records but mine falls to 896.

Makes zero sense to me:

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

Have you looked to see which records are not in the final result? Is there a pattern there? — D Stanley, May 21 '15 at 19:18
Shot in the dark. But try giving the second query a different alias. Also I wonder if some other query result is cached and that's what's giving you the wrong result. Lastly, that ROW_NUMBER() is suspicious. — dotnetN00b, May 21 '15 at 19:18
I would take out the ROW_NUMBER() and just leave the null value in the second query to ensure that you are getting back 1222 records. — FutbolFan, May 21 '15 at 19:44
I doubt it is the cause of your issue, but I notice that you have column-aliases in the bottom query that are different from the corresponding column-alias in the top query. — Tab Alleman, May 21 '15 at 19:49
Noticed that too @TabAlleman, but it shouldn't matter unless he/she tries to order the results (that would throw a syntax error). It would be better to just skip the aliases for the second query though. — Mackan, May 21 '15 at 19:58

score 1 · Accepted Answer · answered May 21 '15 at 21:12

Your queries should return all rows in the table. Unless the table changes between executions, the results from running the subqueries separately should be the same as from running them with the UNION ALL.

As a note, if you want to simplify the query, then 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;

^This was the ticket. Though I ended up using ISNULL function since I did have some variance in data types in that WBS_ELEMENT_ID column. COALESCE kicks out anything that is not of the same data type apparently. — user2891330, May 27 '15 at 14:22
It is true that `ISNULL()` and `COALESCE()` can have different return types. Interesting that this actually affected your application. Here is an article on the topic: http://sqlmag.com/t-sql/coalesce-vs-isnull. — Gordon Linoff, May 27 '15 at 23:27

score 0 · Answer 2 · answered May 21 '15 at 19:58

Obviously there is nothing wrong with your syntax, but if you want to try a different approach to getting your UNION ALL to work with ROW_NUMBER. Here it is:

;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

Stephen Turner · Answer 3 · 2015-05-21T20:52:47.670

0

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 rows on my system...

edited May 21 '15 at 20:52

answered May 21 '15 at 20:47

Stephen Turner

7,125
4
51
68

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

3 Answers3