CodeRang
SQL topics

SQL UNION: combine results with UNION and UNION ALL

SQL UNION combines the results of multiple SELECT statements into one result set. This guide explains UNION vs UNION ALL, column compatibility, duplicate handling, and ordering the final output.

Updated · SQL Arena team

SQL from scratch courseAll problems

What you will practice

  • Combine rows from separate SELECT statements
  • Choose UNION or UNION ALL intentionally
  • Match column counts and compatible data types
  • Find overlap with INTERSECT
  • Subtract one result set with EXCEPT

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Build one list of agent activities

The ticket_events table stores support activity. Return agent_id and source for agents with resolved or escalated events. Label rows from the first group as resolved and rows from the second as escalated. Combine both sets with UNION and sort by agent_id and source.

Query 1PostgreSQLv16
SELECT agent_id, 'resolved' AS source
FROM ticket_events
WHERE event_type = 'resolved'
UNION
SELECT agent_id, 'escalated' AS source
FROM ticket_events
WHERE event_type = 'escalated'
ORDER BY agent_id, source;
Submit
Resultsupport_agentsticket_events
Answer accepted

How SQL UNION and UNION ALL work

The first SELECT filters the event table to resolved records. It returns agent_id plus a text literal, 'resolved', named source. That second column is not stored in the table; it is created so every row carries information about which branch produced it. This is a common pattern when building one report from several sources.

The second SELECT returns the same two-column shape for escalated records. UNION matches columns by position, not by alias or semantic meaning, so both branches need the same number of output columns and compatible types. PostgreSQL then combines them and removes rows that are identical across every selected column. Agent 1 can still appear twice because the source value differs.

The ORDER BY belongs to the combined result and appears only after the final SELECT. If repeated rows were meaningful, UNION ALL would be the better choice because it keeps every row instead of performing duplicate elimination. That distinction matters in event logs, transaction exports, and other data where two identical-looking records may still represent separate occurrences.

SQL UNION and UNION ALL both stack compatible SELECT results vertically. UNION removes rows that are completely identical across the resulting columns, while UNION ALL keeps every row. Each SELECT must return the same number of columns, and corresponding positions need compatible data types. The output column names are generally taken from the first SELECT. To sort the combined result, place the final ORDER BY after the last SELECT rather than between the UNION branches.

SQLPostgreSQL v16
SELECT agent_id, 'resolved' AS source
FROM ticket_events
WHERE event_type = 'resolved'
UNION
SELECT agent_id, 'escalated' AS source
FROM ticket_events
WHERE event_type = 'escalated'
ORDER BY agent_id, source;
Query result
agent_idsource
1escalated
1resolved
2escalated
3escalated
3resolved
4escalated
5escalated
5resolved

Common SQL UNION mistakes

01

Using UNION when duplicates are meaningful

UNION removes duplicate result rows. If two source records must both survive, that changes the data rather than merely formatting it. Use UNION ALL when repetition is part of the result.

02

Returning incompatible column lists

Each branch must produce the same number of columns, and corresponding positions need compatible types. A two-column SELECT cannot be unioned directly with a three-column SELECT.

03

Assuming one matching field makes a duplicate

UNION compares complete result rows. Equal agent_id values are not duplicates when source differs. If uniqueness should be based on only one field, UNION alone does not express that rule.

Practice problems on this topic

SQL from scratch course

Once the basic syntax is clear, set operations become more useful in less obvious cases. SQL Arena includes an Easy exercise that merges status values without repeats, another that builds one register from multiple pieces, and tasks where several query paths must feed the same output. Harder exercises use the same idea inside reshaping and period-based reporting. That progression is useful because UNION is often confused with joins: joins add columns by relating rows, while UNION stacks compatible rows into one result. If you want to build the surrounding fundamentals as well, continue with the “SQL from Scratch” course, where each lesson pairs explanation with a task.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is SQL UNION?

SQL UNION combines rows returned by two or more SELECT statements into one result set. By default, it removes duplicate result rows.

What is the difference between UNION and UNION ALL in SQL?

UNION removes duplicate rows from the combined result. UNION ALL keeps all rows, including duplicates.

Do SQL UNION queries need the same columns?

Each SELECT must return the same number of columns. Corresponding columns also need data types that the database can combine.

How do I use ORDER BY with SQL UNION?

Put ORDER BY after the final SELECT to sort the complete combined result. The ordering then applies to rows produced by all UNION branches.

See also

Practice SQL UNION and UNION ALL

SQL Arena has 1,192 automatically checked tasks for PostgreSQL, MySQL, ClickHouse, and BigQuery. Each task has its own tables, and answers are checked against hidden data rather than a fixed output template. The free plan includes 292 tasks across all four dialects and does not require a card.

Practice SQL UNION

More practice from CodeRang