Eliminating null values in union

user_2018133 0
Viewed 72

I'm doing a query across databases with an identical structure, to show a mapping from a source value to a target value.
Every one of my databases has a table with two columns: source and target

DB1

Source Target
A X
A Y
B NULL
C NULL

DB2

Source Target
A NULL
A Y
B Z

So my query is

Select t.Source, t.Target
from DB1.table t
union
Select t.Source, t.Target
from DB2.table t

What I'm getting is

Source Target
A X
A Y
B NULL
C NULL
B Z
A NULL

But I'm only interested in the target being NULL, if there is no other mapping present. So I'm looking for the following result:

Source Target
A X
A Y
C NULL
B Z

How can I easily eliminate the highlighted rows A | NULL and B | NULL from my results?

I've seen a few answers suggesting using MAX(Target), but that won't work for me since I can have multiple valid mappings for a single source (A | X and A | Y)

sql-null union sql-server sql
3 Answers

Something like this would work, just give a number based on NULL, and select the first:

SELECT  TOP(1) WITH TIES UN.Source
,       UN.Target
FROM    (
            Select t.Source, t.Target
            from DB1.table t
            union
            Select t.Source, t.Target
            from DB2.table t
        ) AS UN
ORDER BY DENSE_RANK()OVER(PARTITION BY UN.Source ORDER BY CASE WHEN UN.Target IS NOT NULL THEN 1 ELSE 2 END)
user_15878190

You might find it easier to think in terms of minimums:

with data as (
    select Source, Target from DB1.<table> union
    select Source, Target from DB2.<table>
), qualified as (
    select *,
        case when Target is not null or min(Target) over (partition by Source) is null
             then 1 end as Keep
    from data
)
select Source, Target from qualified where Keep = 1;
user_13222680

For completeness, here's the solution I went with, based on the answer of @HoneyBadger, including the suggestion made by @MartinSmith in the comments

SELECT * FROM
    (SELECT  UN.Source
    ,       UN.Target
    ,       DENSE_RANK()OVER(PARTITION BY UN.Source ORDER BY CASE WHEN UN.Target IS NOT NULL THEN 1 ELSE 2 END) as ranking
    FROM    (
                Select t.Source, t.Target
                from DB1.table t
                union
                Select t.Source, t.Target
                from DB2.table t
            ) AS UN
    ) UN2
WHERE UN2.ranking = 1
ORDER BY UN2.Source, UN2.Target

This solution selects only the records that have a DENSE_RANK of 1, avoiding the TOP(1) WITH TIES.

user_20181330
Related