Union in Oracle

[dkwong]

I'm trying to union two queries together but they have a different number of result columns (one with 11, one with 9). But I don't want to have to add more columns to the query with the fewer columns because then I'd have to perform an unneeded join. How can I make the Union work? Is there a way of putting in two dummy columns?

 

[jimbopalmer]

Select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11 from A
union
Select col1, col2, col3, col4, col5, col6, col7, col8, col9, ' ', ' ' from B


you could use 0 if the column was numeric or sysdate if they are dates or you could just repeat col9 three times

if there is no chance A and B have duplicate rows and you don't need these sorted, you can use union all I tried to remain child-like, all I acheived was childish.

 

[fmorel]

You can also use NULL values. It may help you to avoid to mix them with "real" values.

If column is a number, use to_number(NULL)
If column is a date, use to_date(NULL)
If column is a char or varchar2, use NULL

Ex:
Select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11 from A
union all
Select col1, col2, col3, col4, col5, col6, col7, col8, col9, To_number(NULL), to_date(NULL)from B

 

[dkwong]

Thanks. Are there any other differences between Union and Union All? If I want to order the results, do I put the order by in the first clause or second clause?

 

[jimbopalmer]

Union does a sort, to eliminate duplicates, so if you need them sorted use union.

Union al is for when YOU know there are no duplicates I tried to remain child-like, all I acheived was childish.

 

[fmorel]

The diffenrence between union and union all is that "union" removes duplicates whereas "union all" does not.

So union all is faster than union.

to sort the results, I think you will have to add a 2nd level in your query

ex:
select Select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11 from
(Select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11 from A
union all
Select col1, col2, col3, col4, col5, col6, col7, col8, col9, To_number(NULL), to_date(NULL)from B)
order by 1,2

 

[sem]

The difference between UNION and UNION ALL is that the primer returns DISTINCT values. You may order only the WHOLE result set, so place ORDER BY clause AFTER the last subquery.