Could you explain the purpose and working principle of the UNION operator in PostgreSQL?

The UNION operator in PostgreSQL is used to combine the results of two or more SELECT queries into a single result set. It returns only unique rows, automatically removing duplicates.

How it works:

• All SELECT queries must return the same number of columns with compatible data types.
• The results are combined into a single set.
• Duplicate rows are removed.

If you want to include duplicates, use UNION ALL.

Example:

SELECT name FROM employees
UNION
SELECT name FROM managers;

This query returns a list of unique names from the employees and managers tables.