The UNION operator in SQL is used to combine the results from two or more SELECT queries into a single result set. When using UNION, the following must be taken into account:
1. Number of Columns: Each SELECT statement must have the same number of columns.
2. Data Types: The corresponding columns must have compatible data types.
3. Order of Columns: The columns in each SELECT statement must be in the same order.
4. Duplicates: By default, UNION removes duplicate rows. If you want to include duplicates, you can use UNION ALL.
Here is the basic syntax of using the UNION operator:
SELECT column1, column2, ...
FROM table1
WHERE condition
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition;
Example
Suppose you have two tables: employees and contractors, and you want to retrieve a list of names from both tables.
SELECT name
FROM employees
WHERE department = 'Sales'
UNION
SELECT name
FROM contractors
WHERE project = 'ProjectA';
In this example:
- The first query selects names from the employees table where the department is 'Sales'.
- The second query selects names from the contractors table where the project is 'ProjectA'.
- The UNION operator combines the results from both queries into one list. Duplicate names will be eliminated.
Using UNION ALL
If you wish to keep duplicates in the result, you can use UNION ALL:
SELECT name
FROM employees
WHERE department = 'Sales'
UNION ALL
SELECT name
FROM contractors
WHERE project = 'ProjectA';
In this case, both unique and duplicate names will appear in the final result set.