SQL UNION statement

SQL UNION statement is used to combine the resulting sets of 2 or more SELECT operators. It removes repetitive strings between different SELECT requests.

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

What is the difference between UNION and UNION ALL

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

Syntax for UNION operator in SQL

SELECT expression1_id, expression2_id, ... expression_n_id
FROM tabs
[WHERE conds]
UNION
SELECT expression1_id, expression2_id, ... expression_n_id
FROM tabs
[WHERE conds]

where:

• expression1_id, expression2_id, expression_n_id – the Stollblock or the calculations that you want to get
• tabs – The tables from which you want to get the records. The FROM sentence must contain at least one table
• WHERE conds – Optional. Conditions to be met for entries to be selected

Note:
Both SELECT operators must have the same number of expressions

The corresponding expressions must have the same data type in SELECT requests. For example: expression1_id must have the same data type in both the first and second SELECT statements. See also the UNION ALL operator.

Example – a single field with the same name

Let’s see how to use a UNION 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
SELECT suppl_id
FROM ords
ORDER BY suppl_id;

In this SQL example of the UNION statement, if suppl_id appears in the suppliers and orders tables, it will be once in your result set. The UNION statement removes duplicates. If you do not want to remove duplicates, try using a UNION ALL 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 fulfilled the following UNION request.

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

You’ll get the following results.

As you can see from this example, UNION took all suppl_id values from the suppliers table as well as the orders table and returned a combined set of results. Since UNION has removed duplicates between the resulting sets, the suppl_id 2000 field is displayed only once, even if it is in the suppliers and orders tables. If you do not want to remove duplicates, try using the UNION ALL operator instead.

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 do not have the same column names between SELECT statements, this becomes a bit more complicated, especially if you want to organize the query results using the ORDER BY statement.

Let’s see how to use the UNION operator with different column names and organize the query results.
For example.

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

In this SQL example UNION, since column names in two SELECT statements are different, it is more advantageous to refer to columns in ORDER BY 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 suppl_id / compa_id fields 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 performed the next UNION operator.

SELECT suppl_id,
suppl_name
FROM suppls
WHERE suppl_id > 2000
UNION
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 supplier_id equal to 3000 appears only once in the result set because the UNION query removed repetitive records.
2. Second, note that the column headers in the result set are called supplier_id and supplier_name. This is because these were the column names used in the first SELECT statement in UNION.

If you wanted, you could assign aliases to the columns as follows.

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

The resulting column headers will now have an alias of ID_Value_id for the first column and Name_Value_id for the second column.

SQL Union and Union All – SQL Training Online