UNION ALL
UNION ALL combines two tables, row by row. Implement UNION ALL by using the SQL panel of Studio Modeler.
Syntax
SELECT columnA [, columnB, ... ]
FROM table1
UNION ALL
SELECT columnA [, columnB, ... ]
FROM table2
Remarks
Multiple column selections can be made, but the number of columns and the column data types should match. All queries in a SQL statement containing the UNION ALL function must have an equal number of expressions in their target lists, as shown in the following example.
Example
SELECT ProductID, ProductName, UnitPrice
FROM /shared/examples/ds_inventory/products products
UNION ALL
SELECT ProductID, ProductName, UnitPrice
FROM /shared/examples/ds_inventory/products products_1
Example (To Contrast with Results of UNION)
Suppose that table T1 has columns C1, C2, and C3, and table T2 has columns Ca, Cb, Cc.
Table T1 has these values.
C1 | C2 | C3 |
001 | Hello | Goodbye |
002 | Hola | Adios |
003 | Aloha | Aloha |
Table T2 has these values.
Ca | Cb | Cc |
003 | Aloha | Aloha |
004 | Alo | Adieu |
007 | Ciao | Arrivederci |
You execute the following query:
SELECT C1 C2 C3 FROM T1
UNION ALL
SELECT Ci Cii Ciii FROM T2
The results returned are shown in the table below.
001 | Hello | Goodbye |
002 | Hola | Adios |
003 | Aloha | Aloha |
003 | Aloha | Aloha |
004 | Alo | Adieu |
007 | Ciao | Arrivederci |
This result set from UNION ALL contrasts with the output of the UNION function, which omits the repeated value of 003.