What are the limitations of using the UNION operator in SQL?

The UNION operator in SQL is used to combine the results of two or more SELECT queries. Main restrictions and features:

• Matching number and types of columns: All SELECTs must return the same number of columns with compatible data types.
• Removing duplicates: UNION by default removes duplicate rows, which can affect performance.
• UNION ALL: if duplicates should be kept, use UNION ALL.
• Column order matters: columns are combined by position, not by name.
• Restrictions on ORDER BY: ORDER BY applies only to the last SELECT or the entire result after UNION.

Example:

SELECT id, name FROM users
UNION
SELECT id, name FROM customers;

If the number of columns or their types do not match, the query will produce an error.