What is the UNION Operator in SQL and How to Use It

The UNION operator is used in SQL to combine the results of two or more SELECT statements into a single result set. The UNION operator can be used to combine data from two or more tables, or it can be used to combine the results of two or more SELECT statements that are run against the same table. 

In this article, we will discuss how to use the UNION operator in SQL and provide some examples.

How to Use the UNION Operator:

The syntax for using the UNION operator in SQL is as follows:

SELECT column1, column2, column3 FROM table1 UNION SELECT column1, column2, column3 FROM table2;

In this syntax, “column1, column2, column3” are the columns that you want to return from the two or more SELECT statements. You can also use the UNION operator to combine the results of two or more SELECT statements that are run against the same table. In this case, the syntax would be as follows:

SELECT column1, column2, column3 FROM table1 UNION ALL SELECT column1, column2, column3 FROM table2;

In this syntax, “column1, column2, column3” are the columns that you want to return from the two or more SELECT statements. The “ALL” keyword tells SQL to include all of the rows from both tables in the result set.

Examples:

Now that we have discussed how to use the UNION operator, let’s take a look at some examples. To know more check RemoteDBA.com

Example 1:

In this example, we will combine the results of two SELECT statements that are run against the same table. The table that we will be using is the “Employees” table in the North wind sample database. The first SELECT statement will return the employee ID, last name, and first name from the table and the second SELECT statement will return the department name from the table. 

The syntax for this example is as follows:

SELECT Employee ID, Last Name, First Name FROM Employees UNION SELECT Department Name FROM Employees;

The result set from this query will be as follows:

Employee ID Last Name First Name Department Name

111 Allred, Mildred Sales

112 Ford, John Sales

213 Reuter, Laura Shipping

214 Heinemeier, Jonas Shipping

315 Kwan, Harvey Accounting

316 Mitchell, Janet Accounting

Example 2:

In this example, we will combine the results of two SELECT statements that are run against different tables. The first SELECT statement will return the employee ID and last name from the “Employees” table and the second SELECT statement will return the employee’s email address from the “Employee Email Addresses” table. 

The syntax for this example is as follows:

SELECT Employee ID, Last Name FROM Employees UNION SELECT Email Address FROM Employee Email Addresses;

The result set from this query will be as follows:

Employee ID Last Name Email Address 

111 Allred, Mildred jill.allred@example.com

112 Ford, John john.ford@example.com

213 Reuter, Laura laura.reuter@example.com

214 Heinemeier, Jonas jonas.heinemeier@example.com

315 Kwan, Harvey harvey.kwan@example.com

316 Mitchell, Janet janet.mitchell@example.com

Example 3:

In this example, we will combine the results of two SELECT statements that are run against different tables. The first SELECT statement will return the employee ID, last name, and first name from the “Employees” table and the second SELECT statement will return the employee’s job title from the “Employee Job Titles” table. 

The syntax for this example is as follows:

SELECT Employee ID, Last Name, First Name FROM Employees UNION SELECT Job Title FROM Employee Job Titles;

The result set from this query will be as follows:

Employee ID Last Name First Name Job Title

111 Allred, Mildred Sales Representative

112 Ford, John Sales Representative

213 Reuter, Laura Shipping Coordinator

214 Heinemeier, Jonas Shipping Coordinator

315 Kwan, Harvey Accounting Manager

316 Mitchell, Janet Accounting Manager

If you need to combine the results of more than two SELECT statements, you can use the UNION ALL operator. This will include all of the rows from all of the SELECT statements in the result set. 

If you need to exclude duplicate rows from the result set, you can use the UNION operator with the DISTINCT keyword. This will remove any duplicate rows from the result set. 

The syntax for using the UNION operator with the DISTINCT keyword is as follows:

SELECT column1, column2, column3 FROM table1 UNION DISTINCT SELECT column1, column2, column3 FROM table2;

column1, column2, and column3 are the columns that you want to include in the result set. 

Conclusion:

The UNION operator is a useful tool for combining the results of multiple SELECT statements. It can be used to combine the results of two or more SELECT statements that are run against the same table, or the results of two or more SELECT statements that are run against different tables.