UNION [ALL] Section (Query Union)

Using the query language you can merge multiple queries. Records obtained in each of the united queries are included into a single query result.

Each query gathers data independently, but operations such as result ordering and totals calculation take place within the united query results.

Query result fields will be named as described in the selection field list of the first united query. Selection fields from the remaining queries are collated with result fields according to their following order in the selection fields list. United queries must have a similar number of fields in the selection fields list.

If united query selection fields have a different type, then query result fields will have compound type.

Query union is described by the following rule:

<Query Union>
	|
	UNION [ALL] <Query Description> [<Query Union>]

Query union begins with the required UNION keyword, followed by the united query description. Then you can join another query, etc.

By default, same strings in the query result formed by different queries are replaced by one string. If you need to keep duplicate strings, use the ALL keyword.

Example:

SELECT
   Invoice.Contractor,
   Invoice.Nomenclature,
   SUM (Invoice.Count) AS Receipt,
   SUM (0) AS Expense

FROM
   Document.ReceiptofGoods.Content AS Invoice

GROUP BY
   Contractor,
   Nomenclature

UNION ALL

SELECT
   Invoice.Contractor,
   Invoice.Nomenclature,
   SUM (0),
   SUM (Invoice.Count)

FROM
   Document.Invoice.Content AS Invoice

GROUP BY
   Contractor,
   Nomenclature

ORDER BY
   Contractor,
   Nomenclature

TOTALS BY
   Contractor

GRAND TOTALS

Query result:

Contractor	Nomenclature	Receipt	Expense
		1 943	151
"Zarya" knitwear factory		443	4
"Zarya" knitwear factory	Children's pants		3
"Zarya" knitwear factory	Children's pants	100
"Zarya" knitwear factory	"Cowboy" shirt	200
"Zarya" knitwear factory	Women's jeans		1
"Zarya" knitwear factory	Women's jeans	80
"Zarya" knitwear factory	Children's sweater	60
"Zarya" knitwear factory	Krups food processor		3
Clothing fair			42
Clothing fair	"Cowboy" shirt		20
Clothing fair	Women's jeans		20
Clothing fair	"Aquarium" bathroom		2
"Budenovski" trade house			53
"Budenovski" trade house	Children's pants		10
"Budenovski" trade house	"Cowboy" shirt		5
"Budenovski" trade house	Women's jeans		12
"Budenovski" trade house	Children's sweater		1
"Budenovski" trade house	"Lilia" sink		8
"Budenovski" trade house	"Aquarium" bathroom		5
"Budenovski" trade house	"Ultra" mixer		10
"Budenovski" trade house	Krups food processor		2
Pavilion 45 in wholesale market			44
Pavilion 45 in wholesale market	"Cowboy" shirt		5
Pavilion 45 in wholesale market	Women's jeans		9
Pavilion 45 in wholesale market	"Lilia" sink		10
Pavilion 45 in wholesale market	"Aquarium" bathroom		20
Bavaria China		1 500	8
Bavaria China	Children's pants		7
Bavaria China	Women's jeans		1
Bavaria China	"Lilia" sink	270
Bavaria China	"Aquarium" bathroom	230
Bavaria China	"Ultra" mixer	1 000