The UNION operator in SQL is used to combine the result sets of two or more SELECT statements into a single result set. Each SELECT statement within the UNION must have the same number of columns, and the corresponding columns must have compatible data types. The UNION operator removes duplicate rows from the final result set by default. Here's a real-time example of how to use the UNION operator with detailed explanations:

Example: Combining Results from Multiple Tables

Suppose you have two tables: employees and contractors, each containing information about individuals working for your company. You want to create a list of all employees and contractors in a single result set.

sqlCopy code
SELECT employee_id, employee_name
FROM employees
UNION
SELECT contractor_id, contractor_name
FROM contractors;

In this example:

• The first SELECT statement retrieves employee_id and employee_name from the employees table.
• The second SELECT statement retrieves contractor_id and contractor_name from the contractors table.
• The UNION operator combines the results of these two queries into a single result set.

The result set will contain all unique combinations of employee_id and employee_name from the employees table and contractor_id and contractor_name from the contractors table.

Explanation:

1. The UNION operator combines the results of two SELECT statements vertically. It stacks the rows from the second query below the rows from the first query.
2. The UNION operator removes duplicate rows from the result set. If there are identical rows in the result sets of both queries, only one of them will appear in the final result.
3. Each SELECT statement within the UNION must return the same number of columns, and the data types of corresponding columns must be compatible. In this example, the first query returns two columns, and the second query also returns two columns with compatible data types.
4. You can use additional SELECT statements with the UNION operator to combine results from more than two tables.
5. If you want to include duplicate rows in the result set, you can use the UNION ALL operator instead of UNION. UNION ALL does not remove duplicates and is generally faster since it doesn't perform the extra step of checking for duplicates.

The UNION operator is useful when you need to combine data from multiple tables with similar structures, such as when you want to create a consolidated list or report that includes information from different sources.