Union operation

In the realm of SQL, the UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement in the UNION must have the same number of columns. The columns should also have similar data types, and they must be in the same order.

Basic Usage of UNION

Here is an example of a basic UNION operation:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

This SQL statement would return the union of the two SELECT statements, which means it would return all the distinct values from both the table1 and table2.

UNION ALL

If you want to allow duplicate values, you can use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

In this case, UNION ALL would return all values from both tables, including duplicates.

Column Names in the Result-set

The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.

SELECT column_name1 AS 'Alias Name', column_name2 FROM table1
UNION
SELECT column_name1, column_name2 FROM table2;

Sorting the Result-set

You can also sort the results of a UNION operation. The ORDER BY clause goes at the end of the query and affects the entire result-set.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2
ORDER BY column_name(s);

This SQL statement would return the combined result-set of both SELECT statements and sort the result-set by the column name(s) specified after the ORDER BY keyword.

Practical Use Case

Let's consider a practical use case. Suppose we have two tables, Orders and Deliveries, and we want to make a list of all cities where orders are made and deliveries are made.

SELECT City FROM Orders
UNION
SELECT City FROM Deliveries
ORDER BY City;

This SQL statement would return a list of cities from both the Orders and Deliveries table, sorted in ascending order. The list will not contain any duplicate city names.

Conclusion

The UNION operator provides a powerful way to combine and manipulate the result-sets of multiple SELECT statements. By understanding how to use UNION and UNION ALL, you can perform more complex queries and extract more meaningful data from your SQL database.