Unions in SQL are a powerful way to manage data, allowing users to combine multiple queries into one. With unions, several queries can be combined to create a single larger query that retrieves data from multiple tables. Unions can retrieve data from different tables, columns or even different databases.
In SQL, a union is a clause that combines the results of two or more SELECT statements into a single result set. It is used when you need to retrieve data from multiple tables or databases and combine it into a single result. It can also be used to combine results from different columns into one set of results.
ALL or DISTINCT
When using a union, you combine multiple SELECT statements into one larger statement using the UNION keyword. The keyword can be followed by either ALL or DISTINCT. The ALL keyword returns all records, including duplicates. The DISTINCT keyword returns only unique results and removes duplicates.
To use a union, each SELECT statement must return the same number of columns and the same data types. The columns must also be in the same order. That is because the union combines the results of each SELECT statement into a single result set, and the database needs the columns to line up.
Finally, you must specify the UNION keyword between the SELECT statements. Unions are a powerful tool for managing data, but they must be used carefully. If the statements do not return the same number of columns, the same data types and the columns in the same order, the union will not work.
To summarise, unions in SQL let users combine multiple queries into one. They are used to retrieve data from multiple tables, columns or databases. They must be used carefully, because the SELECT statements must match in column count, data type and order for the union to succeed.
Sources and further reading
Official documentation and reference material for this topic.