UNION ALL SQL Server operator

UNION ALL SQL Server operator is used to combine the resulting sets of 2 or more SELECT operators. It does not remove repeating rows between different SELECT operators (all rows are returned).

Each SELECT operator in UNION ALL must have the same number of fields in result sets with the same data types.

What is the difference between UNION and UNION ALL?

• The UNION operator removes repetitive rows.
• UNION ALL does not remove repetitive strings.

Syntax for UNION ALL operator in SQL

SELECT expr1, expr2, … expr_n
  FROM tabs
  [WHERE conds]
  UNION ALL
  SELECT expr1, expr2, … expr_n
  FROM tabs
  [WHERE conds];

where:

• expr1,2,_n – Columns or calculations that you want to get
• tabs – The tables from which you want the records. The FROM sentence must contain at least one table
• WHERE conds – It’s optional. Conditions to be met for entries to be selected

Note: Both SELECT requests must contain the same number of expressions

The corresponding expressions must have the same data type in SELECT requests. For example: expr1 must have the same data type in both the first and second SELECT statements.

Example – a single field with the same name
Let’s see how to use a UNION ALL SQL statement that returns a single field. In this simple example, a field in both SELECT statements will have the same name and data type.
For example:

SELECT suppl_id
   FROM suppls
  UNION ALL
 SELECT suppl_id
   FROM orders
ORDER BY suppl_id;

This SQL example UNION ALL will return supp_id several times in the result set if the same value appears in the suppliers and orders tables. The UNION ALL SQL statement does not remove duplicates. If you want to remove duplicates, try using a UNION statement.

Now let’s look at this example, then let’s look at some data.

If you had a table filled with the following entries.

And the table is filled with the following entries.

And you performed the next UNION ALL operator.

SELECT suppl_id
FROM suppls
UNION ALL
SELECT suppl_id
FROM ords
ORDER BY suppl_id;

You’ll get the following results:

As you can see from this example, UNION ALL took all suppl_id values from the suppliers table as well as the orders table and returned a combined set of results. The duplicates have not been removed, as you can see from the suppl_id 2000 value, which appears twice in the result set.

Example – different field names

It is not necessary that the corresponding columns in each SELECT operator have the same names, but they must be of the same corresponding data types.

If you don’t have the same column names in your SELECT statements, this becomes a bit more complicated, especially if you want to organize the query results using the ORDER BY statement.

Let’s take a look at how to use the UNION ALL operator with different column names and organize the query results. For example:

SELECT suppl_id,
       suppl_name
    FROM suppls
  WHERE suppl_id > 2000
  UNION ALL
SELECT com_id, com_name
    FROM coms
  WHERE com_id > 1000
  ORDER BY 1;

In this SQL example UNION ALL, since the column names in the two SELECT operators are different, it is more advantageous to refer to the columns in the ORDER BY sentence by their position in the result set.

In this example, we sort the results by suppl_id / com_id in ascending order as ORDER BY 1. The fields suppl_id / com_id are at position #1 in the result set.

Now let us look at this example in more detail with the data. If you had a table of suppliers filled in with the following entries.

And the table was filled in with the following entries.

And you executed the following query containing a UNION ALL.

SELECT suppl_id,
       suppl_name
    FROM suppls
  WHERE suppl_id > 2000
  UNION ALL
SELECT com_id, com_name
    FROM coms
  WHERE com_id > 1000
  ORDER BY 1;

You’ll get the following results:

1. First, note that a record with suppl_id equal to 3000 appears twice in the result set, since the UNION ALL query returns all rows and does not remove duplicates.
2. Second, note that the column headers in the result set are called suppl_id and suppl_name. This is because these were the column names used in the first SELECT statement in UNION ALL.

If you wanted, you could assign aliases as follows.

SELECT suppl_id AS ID_Val,
       suppl_name AS Name_Val
   FROM suppls
  WHERE suppl_id > 2000
  UNION ALL
SELECT com_id AS ID_Val, com_name AS Name_Val
   FROM coms
  WHERE com_id > 1000
  ORDER BY 1;

Now the column headers will have an alias as ID_Val for the first column and Name_Val for the second column.

Union and union all in sql server