CODEWITHSUNDEEP
sqloracle-databaseunionunion-all
artemis
artemis

Reputation: 7241

UNION ALL not performing as expected - Oracle SQL

I have two tables:

tableA

| Part | Val |
|:----:|:---:|
|  AA  |  3  |
|  AB  |  2  |
|  AC  |  11 |
|  AD  |  6  |
|  AE  |  3  |

tableB

| Part | Val |
|:----:|:---:|
|  AC  |  9  |
|  AF  |  5  |
|  AG  |  1  |
|  AH  |  10 |
|  AI  |  97 |

I would like to union these tables to achieve this result:

| Part | ValA | ValB |
|:----:|:----:|:----:|
|  AA  |   3  |   0  |
|  AB  |   2  |   0  |
|  AC  |  11  |   9  |
|  AD  |   6  |   0  |
|  AE  |   3  |   0  |
|  AF  |   0  |   5  |
|  AG  |   0  |   1  |
|  AH  |   0  |  10  |
|  AI  |   0  |  97  |

I have tried:

SELECT * FROM tableA
UNION ALL
SELECT * FROM tableB

But that results in only one column of vals, which I do not want.

How can I merge these tables and create two columns, one for each table, where if the part does not appear in the other table, its value can just be 0?

SQL FIDDLE for reference.

Upvotes: 0

Views: 98

Answers (2)

Justin Cave
Justin Cave

Reputation: 231651

It appears that you want to join the tables, not union them

select nvl(a.Part, b.Part) as Part, 
       nvl( a.Val, 0 )     as ValA, 
       nvl( b.Val, 0 )     as ValB
  from tableA a
       full outer join tableB b 
         on( a.Part = b.Part )
 order by 1

Note that using case-sensitive identifiers like you do in your fiddle is generally frowned upon. It tends to make writing queries more complicated than it needs to be and it tends to get annoying to have to include the double quotes around every column name.

Demo

Upvotes: 5

Fahmi
Fahmi

Reputation: 37473

You can try below -

   select part,max(valA),max(valB) from
   (
      select part, val as valA, 0 as valB from tableA
      union all
      select part, 0 , val  from tableB
   )A group by part

Upvotes: 0

Related Questions