UNION operator

UNION Operator in SQL

UNION operator is quite useful. It is used with SQL to merge result sets from two or more SELECT queries. The result is a solo result set. It defaults to removing duplicate rows. However, you could opt for UNION ALL. This will allow for duplicates.

Queries need to align. They should match in number and order of columns. If not compatible, you cannot proceed. You can use compatible columns, and the queries will still run. Ultimately, the results will merge into a single result set.

Formula for UNION:

SELECT column1, column2, ... FROM table1 --UNION [ALL]
SELECT column1, column2, ... FROM table2 --[UNION [ALL]
SELECT column1, column2, ... FROM table3 --...;

column1, column2, ... denote the columns. They are the ones you're looking to select. table1, table2, ... refer to the tables. They could also be queries. You want to fetch data from them.

Example: Combining Employees and Managers Tables

Let's consider two tables: Employees and Managers.

Table: Employees

EmployeeID	Name
1	Alice
2	Bob
3	Charlie

Table: Managers

ManagerID	Name
101	David
102	Alice
103	Eve

SQL Query using UNION:

SELECT EmployeeID, Name FROM Employees UNION SELECT ManagerID, Name FROM Managers;

Result:

EmployeeID	Name
1	Alice
2	Bob
3	Charlie
101	David
102	Alice
103	Eve

This example shows the combination of result sets. We used the Employees and Managers tables and applied the UNION operator. This removed duplicates. If you prefer to include duplicates, use UNION ALL.

Important Notes:

• The number and data types of columns must be the same.
• Columns should match the order.
• This is necessary for each SELECT statement.