UNION operator

The results of executing SQL queries can be combined. There is a UNION operator for this.

General query structure with the UNION operator

SELECT table_fields FROM list_of_tables ...
UNION [ALL] 
SELECT table_fields FROM list_of_tables ... ;

In the above-described structure of the query, optional parameters are shown in square brackets.

UNION removes repetitions in the resulting table by default. There is an optional ALL parameter for repeat display.

  • Do not confuse query join operations with table join operations. To do this, use theJOINoperator.
  • Do not confuse query merge operations with subqueries. Subqueries are executed for linked tables.

Table joining with the UNION operator is performed for tables that are not related in any way, but with a similar structure.

To UNION it worked correctly needed: so that the resulting tables of each of SQL queries had the same number of columns, with the same data type and in the same sequence.

There are two other operators whose behavior is very similar to UNION:

  • INTERSECT
    Combines two SELECT queries, but returns only the first SELECT records that have matches in the second SELECT element.
  • EXCEPT
    Combines two SELECT queries, but returns only the first SELECT records, which do not match in the second SELECT element.

Examples of using

For example, it is necessary to display the name of all goods and the names of all family members (very conditional task). Since the data types are the same, we can do this.

SELECT DISTINCT Goods.good_name AS name FROM Goods
UNION
SELECT DISTINCT FamilyMembers.member_name AS name FROM FamilyMembers;
Nested Queries
Syntax of INSERT operator