CODEWITHSUNDEEP
sqloracle-database
evgeni
evgeni

Reputation: 1755

Oracle: Union columns if non-null value exists

I have been experimenting a bit but can't find the right way to do this.

My query looks like this:

select name, value from table1
union select name, value from table2

Currently my query returns the below:

Name  | Value
-------------  
Name1 | null
Name1 | value1
Name1 | value2
Name2 | null

The null values in the above result come only from table1 and I would like to return null values only if the respective name has no non-null values, e.g.:

Name  | Value
-------------  
Name1 | value1
Name1 | value2
Name2 | null

Any suggestions on how to perform this query?

Upvotes: 0

Views: 2939

Answers (3)

Frank Schmitt
Frank Schmitt

Reputation: 30845

You could also use analytic functions to compute the number of NULL values for the name and filter by that:

with v_data as (
  select name, value from table1
  union
  select name, value from table2
)
select v2.* from (
  select 
    v1.*, 
    count(value) over (partition by name) as value_cnt
  from v_data v1
) v2 where value_cnt = 0 or value is not null

Upvotes: 0

user743382
user743382

Reputation:

A simpler approach than the answers you have so far, IMO, would be to explicitly exclude null values from the results for those names where a non-null value exists. It can be written as

with unioncte as (
select name, value from table1
union
select name, value from table2
)
select name, value from unioncte
minus
select name, null from unioncte
where value is not null;

Upvotes: 2

Oscar Pérez
Oscar Pérez

Reputation: 4397

You could use a where:

SELECT *
  FROM (
        SELECT name, value 
          FROM table1
         UNION 
        SELECt name, value 
          FROM table2
       ) s1
 WHERE s1.value IS NOT NULL
    OR s1.name NOT IN (
                      SELECT name
                        FROM table1
                       WHERE value IS NOT NULL
                       UNION
                      SELECT name
                        FROM table2
                       WHERE value IS NOT NULL
                  )

Upvotes: -1

Related Questions