UNION ALL operator

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.