SQL Postgres union data with missed values-CodePudding

I have two results of queries:

id  |  col1  |  col2  |  col3
1     1           null       3j
2     2           12         35
3     null        32         31
4     null        43         33
5     null        44         4

id  |  col1  |  col2  |  col3
6     1        null      3j
7     2        null      35
8     3        null      31
9     4        null      33
10    5        null      null

I need to do union:

id  |  col1  |  col2  |  col3
6     1        null      3j
7     2        12        35
8     3        32        31
9     4        43        33
10    5        null      null
5     null     44        4

The problem is some values are missing

I wrote this big sql query to solve this problem:

select *
from (
         select max(id)   as id,
                max(col1) as col1,
                max(col2) as col2,
                max(col3) as col3
         from (
                  select max(id)   as id,
                         max(col1) as col1,
                         max(col2) as col2,
                         max(col3) as col3
                  from (
                           select max(id)   as id,
                                  max(col1) as col1,
                                  max(col2) as col2,
                                  max(col3) as col3
                           from (
                                    select *
                                    from t1
                                    where id = 1
                                    union
                                    select *
                                    from t2
                                    where id = 2
                                ) t
                           group by case
                                        when col1 is null
                                            or
                                             length(col1) =
                                             0 then id
                                        else col1 end
                       ) t1
                  group by case
                               when col2 is null
                                   or length(col2) = 0
                                   then id
                               else col2 end
              ) t2
         group by case
                      when col3 is null
                          or length(col3) = 0 then id
                      else col3 end
     ) t3

may be are there some ideas to simplify it? Or are there other approaches to enrich data efficiently, because I also need to do intersection, right, left, inner union and I don't want to build so monsters queries

CodePudding user response：

well you cat try something like this: union

select max(col1),
       max(col2),
       max(col3)
from t1
where id = 1
   or id = 2
group by coalesce(nullif(col1, ''),
                  nullif(col2, ''),
                  nullif(col3, ''));

upd: outer union

select max(col1),
       max(col2),
       max(col3)
from t1
where id = 1
   or id = 2
group by coalesce(nullif(col1, ''),
                  nullif(col2, ''),
                  nullif(col3, ''))
having count = 1;

inner union

select max(col1),
       max(col2),
       max(col3)
from t1
where id = 1
   or id = 2
group by coalesce(nullif(col1, ''),
                  nullif(col2, ''),
                  nullif(col3, ''))
having count > 1;

left and right are outer intersect with common query with 'where'