SQL UNION operator allows users to combine the results of two or more SELECT statements into a single result set. The UNION operator returns only distinct values by default, making it a useful tool for merging multiple tables or queries while eliminating any duplicates.
Syntax
The syntax for using UNION in SQL is as follows:
SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2
The above syntax specifies two SELECT statements separated by the UNION operator. Each SELECT statement retrieves data from a different table or a different subset of data within the same table. The results of these SELECT statements are combined to produce a single result set.
It is important to note that the columns in each SELECT statement must match in number and data type. If the column names are different in the two SELECT statements, the first SELECT statement’s column names will be used for the result set.
Example
Here is an example of using UNION in SQL:
SELECT first_name, last_name, email FROM customers UNION SELECT first_name, last_name, email FROM employees
In this example, we are selecting the first name, last name, and email columns from two different tables, customers and employees. The UNION operator combines the results of the two SELECT statements, returning a single result set that includes all the unique rows from both tables. If there are any duplicate rows between the two tables, they will be eliminated in the result set.
Using UNION you can combine data from different tables or queries into a single result set. This can be useful in a variety of situations, such as when you need to combine sales data from multiple stores, or when you need to merge data from two different databases. With its ability to eliminate duplicates and combine data from multiple sources, UNION is an essential operator in SQL.