What are the main limitations when performing a Union operation in SQL?

Main restrictions when performing the UNION operation in SQL:

1. Number of columns: The SELECT queries must contain the same number of columns.
2. Order of columns: Corresponding columns in both queries must have compatible (or identical) data types. The order of columns must also be the same.
3. Column names: The result of UNION inherits column names from the first SELECT query.
4. UNION ALL versus UNION: UNION by default removes duplicate rows, which can be resource-intensive for large datasets. UNION ALL includes all rows, including duplicates, and is usually more performant.

Example:

SELECT id, name, city
FROM employees
UNION
SELECT id, name, '' AS city -- Added an empty string column to match the number of columns
FROM contractors
WHERE type = 'external'; -- Example condition that may lead to different data types without explicit casting