What is the UNION operator in SQL and what data does it compare?

The UNION operator combines the results of two or more SELECT statements.

It compares and merges rows from the result sets. To successfully use UNION:

• The number of columns in each SELECT statement must be the same.
• The data types of the corresponding columns in each SELECT statement should be compatible (though not necessarily identical). For example, you can combine INT and FLOAT, but not INT and BLOB.

UNION by default removes duplicate rows from the combined result. To include duplicates, use UNION ALL.

Example:

-- Get all customer and supplier names, excluding duplicates
SELECT Name FROM Customers
UNION
SELECT Name FROM Suppliers;

Example with UNION ALL:

-- Get all customer and supplier names, including duplicates
SELECT Name FROM Customers
UNION ALL
SELECT Name FROM Suppliers;