Difference between Union and Union all in SQL

Union operator:

Combine the results of two or more queries into a single result set that contains all rows of all queries in the Union query. This is different from using join to combine columns in two tables.

The two basic rules for combining two query result sets using union are:

The columns and columns in all queries must be in the same order.

The data type must be compatible.

This multi-result query combination is very convenient for a single result set to be applied in practice. But there are also problems in the application. The following examples can further explain the problem.

Problem description:

To further analyze and count the production progress of key components in an enterprise, a key_item_cal table is used. Its structure is as follows:

 

Through joint query, the query is divided into three aspects:

1. Propose basic details of the data.CodeAs follows:

Select DBO. key_item.key_item_name as item_name,

DBO. h_morve.qty_recvd1 as quantity, DBO. key_item.style as style

From DBO. key_item left Outer Join

DBO. h_morve on DBO. key_item.key_item = DBO. h_morve.item

Where (DBO. key_item.key_item <> 1)

Order by style ASC

 

2. The code for proposing a level-1 Summary of data is as follows:

Select DBO. key_item.key_item_name as item_name,

Sum (DBO. h_morve.qty_recvd1) as quantity, max (DBO. key_item.style) as style

From DBO. key_item left Outer Join

DBO. h_morve on DBO. key_item.key_item = DBO. h_morve.item

Where (DBO. key_item.key_item <> 1)

Group by DBO. key_item.style, DBO. key_item.key_item_name

Order by style

The result is as follows:

 

3. Put forward the second-level Summary details of the data. The Code is as follows:

Selectmax (DBO. key_item.key_item_name) as item_name,

Sum (DBO. h_morve.qty_recvd1) as quantity,

DBO. key_item.style as style

From DBO. key_item left Outer Join

DBO. h_morve on DBO. key_item.key_item = DBO. h_morve.item

Group by DBO. key_item.style

Order by DBO. key_item.style ASC

Result:

 

The overall design is shown above, but there are new problems when using Union join. The following example shows the difference between Union and Union all.

Solution 1: the code is as follows:

Select DBO. key_item.key_item_name as item_name,

DBO. h_morve.qty_recvd1 as quantity, DBO. key_item.style as style

From DBO. key_item left Outer Join

DBO. h_morve on DBO. key_item.key_item = DBO. h_morve.item

Where (DBO. key_item.key_item <> 1)

Union (

Select DBO. key_item.key_item_name as item_name,

Sum (DBO. h_morve.qty_recvd1) as quantity, max (DBO. key_item.style) as style

From DBO. key_item left Outer Join

DBO. h_morve on DBO. key_item.key_item = DBO. h_morve.item

Where (DBO. key_item.key_item <> 1)

Group by DBO. key_item.style, DBO. key_item.key_item_name

Union

Select max (DBO. key_item.key_item_name) as item_name, sum (DBO. h_morve.qty_recvd1) as quantity,

DBO. key_item.style as style

From DBO. key_item left Outer Join

DBO. h_morve on DBO. key_item.key_item = DBO. h_morve.item

Group by DBO. key_item.style)

Order by DBO. key_item.style ASC

Go

The result is as follows:

 

Problem:

Based on the above results, we can find that the details of the racks after gr180.14.2.1 and after py165 G. 14.2.1b and after py165k. 14.2.1 are obviously different from the second-level summary values. The second-level summary value is correct. Why is the details different from the summary value?

I used the second solution to solve this problem.

Solution 2: the code is as follows:

Select DBO. key_item.key_item_name as item_name,

DBO. h_morve.qty_recvd1 as quantity, DBO. key_item.style as style

From DBO. key_item left Outer Join

DBO. h_morve on DBO. key_item.key_item = DBO. h_morve.item

Where (DBO. key_item.key_item <> 1)

Union all (

Select DBO. key_item.key_item_name as item_name,

Sum (DBO. h_morve.qty_recvd1) as quantity, max (DBO. key_item.style) as style

From DBO. key_item left Outer Join

DBO. h_morve on DBO. key_item.key_item = DBO. h_morve.item

Where (DBO. key_item.key_item <> 1)

Group by DBO. key_item.style, DBO. key_item.key_item_name

Union

Select max (DBO. key_item.key_item_name) as item_name,

Sum (DBO. h_morve.qty_recvd1) as quantity,

DBO. key_item.style as style

From DBO. key_item left Outer Join

DBO. h_morve on DBO. key_item.key_item = DBO. h_morve.item

Group by DBO. key_item.style)

Order by DBO. key_item.style ASC

Go

Result:

 

Through the above example, we can see the difference in the application of union and Union all.