The results of executing SQL queries can be combined. There is a UNION operator for this.
SELECT table_fields FROM list_of_tables ... UNION [ALL] SELECT table_fields FROM list_of_tables ... ;
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 the JOIN operator.
- 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.
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;