8 Combining Result Sets with UNION Operator

The UNION operator is used to combine the result sets of two or more SELECT statements. The SELECT statements used with UNION must have the same number of columns and compatible data types. The resulting output will be the combination of the rows returned by the SELECT statements. In this post, we will explore how to use the UNION operator in SQL with some examples.

Syntax

The basic syntax of UNION operator is as follows:

SELECT column1, column2, ..., columnN
FROM table1
UNION [ALL]
SELECT column1, column2, ..., columnN
FROM table2
UNION [ALL]
...
SELECT column1, column2, ..., columnN
FROM tableN;
  

The keyword UNION combines the result sets of two or more SELECT statements, and removes any duplicate rows from the final result set. If you want to include duplicate rows in the final result set, you can use the keyword UNION ALL.

Example

Let's assume we have two tables: customers and employees, and we want to combine the results of two SELECT statements to get a list of all employees and customers. The two tables have the following columns:

customers(customer_id, customer_name, customer_email)
employees(employee_id, employee_name, employee_email)
  

The following query will combine the results of two SELECT statements to produce a list of all employees and customers:

SELECT customer_name AS name, customer_email AS email
FROM customers
UNION
SELECT employee_name AS name, employee_email AS email
FROM employees;
 

The UNION operator combines the result sets of the two SELECT statements, and removes any duplicate rows from the final result set. The resulting output will have the columns name and email, and will contain all the distinct values of customers and employees.

Conclusion

The UNION operator is a powerful tool in SQL that allows you to combine the result sets of two or more SELECT statements into a single result set. It can be used to combine data from different tables, or to combine data from the same table with different conditions. By using the UNION operator, you can create complex queries that produce the exact results you need.