CODEWITHSUNDEEP
sqloracle-databasejoinleft-joinunion
LiefLayer
LiefLayer

Reputation: 1073

Oracle SQL union table1 with table2 with null result if table2 contains more

I will try to explain with an example.

I have 2 tables:

table1
column1 column2 column3
000001   ABC     COD1
000001   ABC     COD2
000002   BCD     COD3
000003   EDF     COD1
000003   EDF     COD3
000004   FGH     COD1
000004   FGH     COD2
000004   FGH     COD3
000004   FGH     COD4

table2
column3
COD1
COD2
COD3
COD4
COD5
COD6
COD7

table2 only contains all the possible code of column3 of table1 (and only that). table1 contains my real data. I don't want table2 COD5 COD6 and COD7

I have to get a result like this one

000001   ABC     COD1
000001   ABC     COD2
null     null    COD3
null     null    COD4
null     null    COD1
null     null    COD2
000002   BCD     COD3
null     null    COD4
000003   EDF     COD1
null     null    COD2
000003   EDF     COD3
null     null    COD4
000004   FGH     COD1
000004   FGH     COD2
000004   FGH     COD3
000004   FGH     COD4

I need to do this with oracle SQL but I'm not sure if it's possible. I can accept that the first column is always my current column1 (even when column2 is null) if necessary. I don't want duplicate for example two 000004 FGH COD4 but I want duplicate if null.

I tried everything I know... but the best this I was able to do is a union with a minus or a right join on a subquery where I need to specify a column1 condition.

EDIT this is the correct answer found by a friend while I was trying

select * from
(select distinct a.col1, a.col2
from table1 a
right join (select col3 from table2 f
 where f.col3 IN ('COD1','COD3','COD6')) b
on a.col3 = b.col3),
(select col3 from table2 f
 where f.col3 IN ('COD1','COD3','COD6'));

Upvotes: 1

Views: 601

Answers (4)

ch_g
ch_g

Reputation: 1440

The following query will do the trick

    with t2 as (
    select c3
      from t2
     where c3 in ('COD1', 'COD2', 'COD3', 'COD4')
    )
   ,t3 as (
    select distinct
           c1
          ,c2
      from t1
   )    
  ,t_all as (
   select t3.c1
         ,t3.c2
         ,t2.c3
     from t3
         ,t2 
   )
   select t1.c1
         ,t1.c2
         ,t_all.c3
     from t_all left join t1 on 
          t1.c1 = t_all.c1 and t1.c2 = t_all.c2 and t1.c3 = t_all.c3

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21075

You are looking for a partition outer join a "new" feature out there from 2004 but still not widely used.

The expectation is, that the table1 contains for all values of the column3 at least one value, but the column2 is sparse and must be filled. (note, that you even do not need the table2 at all)

The query is straightforward only as you expect the column2 to be NULL in case of adding it, you must reset it with a casestatement:

select 
  tab.column1, 
  case when tab.column1 is NOT NULL then tab.column2 end column2, 
  c3.column3
from tab
partition by (column2)
right outer join 
(select distinct column3 from tab) c3
on tab.column3 = c3.column3
order by tab.column2, c3.column3;

This produces the data as requested:

COLUMN COL COLU
------ --- ----
000001 ABC COD1
000001 ABC COD2
           COD3
           COD4
           COD1
           COD2
000002 BCD COD3
           COD4
000003 EDF COD1
           COD2
000003 EDF COD3
           COD4
000004 FGH COD1
000004 FGH COD2
000004 FGH COD3
000004 FGH COD4

Note, that the manuall solution (i.e. before the partitioned outer join) is to get all combination of the columns 2 and 3 from table1 - see the subquery comb bellow.

In the second step simple outer jointhe combto your table

with col2 as(
select distinct column2 from tab),
col3 as (
select distinct column3 from tab),
comb as (
select *
from col2 cross join col3)
select tab.column1, tab.column2, comb.column3 from comb
left outer join tab 
on comb.column2 = tab.column2 and comb.column3 = tab.column3
order by comb.column2, comb.column3
;

This gives the same result.

Upvotes: 1

Popeye
Popeye

Reputation: 35910

You need to use the cross join and left join as follows:

SQL> with table1 (col1,col2,col3) as
  2  (select '000001','ABC','COD1' from dual union all
  3  select '000001','ABC','COD2' from dual union all
  4  select '000002','BCD','COD3' from dual union all
  5  select '000003','EDF','COD1' from dual union all
  6  select '000003','EDF','COD3' from dual ),
  7  TABLE2 (COL3) AS
  8  (SELECT 'COD1' FROM DUAL UNION ALL
  9  SELECT 'COD2' FROM DUAL UNION ALL
 10  SELECT 'COD3' FROM DUAL UNION ALL
 11  SELECT 'COD4' FROM DUAL UNION ALL
 12  SELECT 'COD5' FROM DUAL)
 13  select t11.col1, T11.COL2, T2.COL3
 14  from (SELECT DISTINCT COL1 FROM TABLE1) T1
 15  CROSS JOIN (SELECT * FROM TABLE2 WHERE COL3 IN (SELECT COL3 FROM TABLE1)) T2
 16  LEFT JOIN TABLE1 T11 ON T11.COL1 = T1.COL1 AND T2.COL3 = T11.COL3
 17  ORDER BY T1.COL1, T2.COL3;

COL1   COL COL3
------ --- ----
000001 ABC COD1
000001 ABC COD2
           COD3
           COD1
           COD2
000002 BCD COD3
000003 EDF COD1
           COD2
000003 EDF COD3

9 rows selected.

SQL>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You can use cross join to generate the rows and then left join:

select t1.col1, t1.col2, c3.col3
from (select distinct col1 from table1) c1 cross join
     table2 c3 left join
     table1 t1
     on t1.col1 = c1.col1 and t1.col3 = c3.col3
order by c1.col1, c3.col3;

Note: It seems strange that you have NULL values in the first two columns. That results in rows that are exactly duplicated. If you use select c1.col1, t1.col2, c3.col3 you will have valid values in the first column.

Upvotes: 1

Related Questions