Union and UNION ALL usage

At work, there is a UNION all in the Oracle statement written before the colleague, and it is used in many places. Then on the Internet to check the usage, a bit of their own understanding.

Union (Union) merges two or more result sets.

When used, two result sets must have the same columns, and the field types need to be consistent.

Select Id,name from TableA
UNION ALL
Select name from TableB

MSG 205, Level 16, State 1, line 1th
All queries that are combined using the UNION, INTERSECT, or EXCEPT operators must have the same number of expressions in their target list.

The difference between Union and union all;

Union removes the repetition of the result set, which is equivalent to a distinct (deduplication);

The Union all will merge the results together, regardless of whether they are duplicated.

TableA

ID Name Score

1 A 80

2 B 79

3 C 68

TableB

ID Name Score

1 D 48

2 e 23

3 C 86

Using Union

1. The results are as follows: Name

Select name from TableA a

Union b

Select name from TableB C

D

E

We run

Select Id,name from TableA

Union

Select Id,name from TableB

The results are as follows:

ID Name

1 A
1d
2b
2e
3c

There are 3 C in two tables, only once when using union.

And we'll see that the Union will sort by default in the first column.

Use UNION ALL

1.

Select name from TableA
UNION ALL
Select name from TableB

Results:

Name

A
B
C
D
E
C

2.

Select Id,name from TableA
UNION ALL
Select Id,name from TableB

The results are as follows:

ID Name

1 A
2b
3c
1d
2e
3c

As seen from the results, the two union all results differ only in whether the output ID has its output order, for TableA all records are immediately tableb all records, so that the union all is not sorted out.

The above usage should be found in many places.

Let me tell you about the problems I have encountered.

In the business you need to query two columns, and two different columns are fetched from the two tables.

Selectt.d day_id,sum(t.own_cost) own_cost,sum(T.cishu) Cishu from (    SelectTo_char (F.riqi,'YYYY-MM-DD') d,sum(NVL (F.feiyong1,0))    + sum(NVL (F.feiyong2,0)) Own_cost,--Amount    0Cishu fromTableA T, TableB FwhereT.liushuihao=F.liushuihaoGroup  byTo_char (F.JI_FEI_RQ,'YYYY-MM-DD')     Union  All         SELECTTo_char (Jiaoyiriqi,'YYYY-MM-DD') d,0Own_cost,COUNT( Case     whenJiao_yi_lx= 1  Then --"transaction type, 1 positive trades, 2 counter trade"    1    End)- COUNT( Case     whenJiao_yi_lx= 2  Then --"transaction type, 1 positive trades, 2 counter trade"    1    End) Cishu fromTableAGroup  byTo_char (Jiaoyiriqi,'YYYY-MM-DD')) TGroup  byT.d

The code above sees a number of times and amounts in two subqueries. When the subquery calculates the amount, the default setting is 0 (0 times) and the amount is 0 (0 own_cost) When the count is calculated.

The benefits of this write:

1, when the number of problems, we only need to see the number of sub-query calculation of the part, in the same vein, the amount of error when we only need to see the relevant code.

2. In a subquery, setting a value that does not evaluate to 0 will not have an effect on the result of the operation.

The above is my simple understanding of Union, welcome to the great God guidance.

Union and UNION ALL usage