In SQL, the UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. The result set produced by a UNION operation contains all the distinct rows from the combined SELECT statements.
The basic syntax for using UNION is as follows:
SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2;
Here’s an explanation of each part of the syntax:
SELECT column1, column2, ...: Specifies the columns you want to select from the tables involved in the UNION operation. The columns selected in each SELECT statement must be compatible in terms of data types.
FROM table1: Specifies the first table or query from which you want to select rows.
UNION: The UNION keyword combines the result sets of multiple SELECT statements.
SELECT column1, column2, ...: Specifies the columns you want to select from the second table or query.
FROM table2: Specifies the second table or query from which you want to select rows.
It’s important to note that the UNION operator removes duplicate rows from the final result set. If you want to include duplicate rows, you can use the UNION ALL operator instead of UNION.
Here’s an example to illustrate the usage of UNION:
SELECT name, age FROM employees WHERE department = 'HR' UNION SELECT name, age FROM contractors WHERE department = 'HR';
In this example, the query selects the name and age columns from both the “employees” and “contractors” tables for the employees and contractors working in the HR department. The UNION operator combines the result sets of both SELECT statements, removing any duplicate rows, and returns a single result set.