The difference between Union and UNION ALL in SQLServer2000
UNION operator:
Combining the results of two or more queries as a single result set, the result set contains all the rows of all queries in the joint query. This is different from the columns in two tables using the join combination.
Two basic rules for the result set of two queries using UNION combine are:
The number of columns and columns in all queries must be the same.
The data type must be compatible.
This multi-range query combination is very convenient to apply a single result set in practice. However, there is a problem in the application, and the following examples can further explain the problem.
Problem Description:
In order to further analyze the production progress of key components in statistical enterprises, a table key_item_cal is used, and its structure is as shown in Figure 1:
Figure one
Through joint inquiry, the query is divided into three aspects:
1. Basic details of the proposed data, 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)
Order by Style ASC
The result is:
2. The first-level summary of the data is submitted, and its code 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 shown in the figure:
3. The second-level summary of the proposed data is as follows:
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
The result is as shown in the figure:
The overall design is as shown above, but new problems appear when connected by UNION. With the following example, you can see 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.IITEMWHERE (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 results are as follows:
problem:
Through the above results, the rear rack of GR180.14.2.1 can be found, the decay of the rear rack and PY165K.14.2.1 after PY165K.14.2.1 is obviously incompatible with its secondary summary. The value of the secondary summary is correct, why do you do not match the summary?
In response to this problem, I adopted a second solution.
Solution 2, its 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
The result is as shown in the figure:
Through the above example, you can see that the Union and UNION ALL are different from the application.
I am underlying undergraduate students, I am willing to engage in corporate information informationization, I hope that the enterprises who need useful people need to contact me.
Yi si children: leew4u@126.com
I am waiting for it.
