The UNION
ALL operator merges several compatible input
streams without performing any duplicate elimination. Every data
row that enters the UNION ALL operator
is included in the operator’s output stream.
The UNION ALL operator
is a nary operator that displays this message:
UNION ALL OPERATOR has N children.
N is the number of input streams into the operator.
This example demonstrates the use of UNION
ALL:
select * from sysindexes where id < 100 union all select * from sysindexes where id > 200
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1 The type of query is SELECT. 3 operator(s) under root |ROOT:EMIT Operator (VA = 3) | | |UNION ALL Operator (VA = 2) has 2 children. | |
| | |SCAN Operator (VA = 0) | | | FROM TABLE | | | sysindexes | | | Using Clustered Index. | | | Index : csysindexes | | | Forward Scan. | | | Positioning by key. | | | Keys are: | | | id ASC | | | Using I/O Size 2 Kbytes for index leaf pages. | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | Using I/O Size 2 Kbytes for data pages. | | | With LRU Buffer Replacement Strategy for data pages. | | | | |SCAN Operator (VA = 1) | | | FROM TABLE | | | sysindexes | | | Using Clustered Index | | | Index : csysindexes | | | Forward scan. | | | Positioning by key. | | | Keys are: | | | id ASC | | | Using I/O Size 2 Kbytes for index leaf pages. | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | Using I/O Size 2 Kbytes for data pages. | | | With LRU Buffer Replacement Strategy for data pages.
The UNION ALL operator
starts by fetching all rows from its leftmost child. In this example,
it returns all of the sysindexes rows with
an ID less than 100. As each child operator’s datastream
is emptied, the UNION ALL operator
moves on to the child operator immediately to its right. This stream
is opened and emptied. This continues until the last (the Nth)
child operator is emptied.
