UNION
UNION is one of the four basic ‘set operators’ in SQL. The UNION operator is used to combine the results of two or more SELECT Statement queries into a single result-set, excluding duplicate values. Using the UNION operator will not allow duplicate values in the results. (See UNION ALL for results allowing duplicate values.) There are specific protocols to follow when using UNION in a query:
Each SELECT statement used with the UNION operator must have the same exact number of columns.
The fields used in the SELECT statements must be in the same order as one another.
The fields used in each SELECT statement must have corresponding data types, meaning they must be alike.
Syntax example
SELECT column_1, column_2, column_N
FROM table_A
UNION
SELECT column_1, column_2, column_N
FROM table_B;Note: ‘column_1’ in each SELECT statement MUST be similar data types, ‘column_2’ in each SELECT statement must also be of similar data types, etc.
UNION ALL
UNION ALL is also a ‘set operator’ in SQL Server. The UNION ALL operator performs -in a similar way to the UNION operator, in the sense that it too combines the result sets of two or more SELECT statements. The difference lies within the results; UNION ALL allows duplicate records in the results as UNION does not.
Syntax example
SELECT column_1, column_2, column_N
FROM table_A
UNION ALL
SELECT column_1, column_2, column_N
FROM table_B;
