How can you combine the results of two or more SELECT queries?

Instruction: Describe the use of the UNION operator and provide an example query.

Context: This question tests the candidate's knowledge of the UNION operator, which is used to combine the results of two or more SELECT queries into a single result set.

Updated

Official Answer

UNION combines query results and removes duplicate result rows. UNION ALL keeps every row, including repeats. Inputs need the same number of columns in corresponding positions and compatible types; column names do not align the inputs automatically.

A self-contained PostgreSQL example:

SELECT 1 AS customer_id
UNION
SELECT 1 AS customer_id
UNION
SELECT 2 AS customer_id
ORDER BY customer_id;

It returns 1, 2. Replacing both UNION operators with UNION ALL returns 1, 1, 2. Duplicate removal applies to the whole selected row. Use ALL when repeats represent meaningful records or when duplicate removal is unnecessary; it avoids that additional work. The final ORDER BY defines output order.

Source

PostgreSQL: combining queries.

Related Questions