Difference Between UNION and UNION ALL in SQL

8
3

Introduction

An event may keep separate lists of people who attended its online and onsite sessions. Some people appear in both lists. When combining them, should those people appear once, or should every attendance entry remain?

SQL provides UNION and UNION ALL for combining query results. UNION removes duplicate rows, while UNION ALL preserves every occurrence. The right choice depends on what the combined result needs to represent.

Understanding the Difference with an Example

Consider the two attendance tables in the image:

  • online_attendees contains Asha, Kabir, and Meera.

  • onsite_attendees contains Kabir, Meera, and Ravi.

The same attendee ID identifies the same person in both tables. Since Kabir and Meera appear in both lists, the inputs contain six attendance entries for four distinct people.

Union vs Union All

Union vs Union All

What UNION does

Look at the result on the left. UNION keeps one copy of each distinct row:

  • Kabir and Meera appear once each: Their attendee ID and name pairs are repeated across the two inputs, so the extra copies are removed.

  • Asha and Ravi remain: Each appears in only one input, so neither row has a duplicate to remove.

The result contains four rows, making it suitable for a combined participant list.

What UNION ALL does

Look at the result on the right. UNION ALL keeps all three online entries and all three onsite entries.

Kabir and Meera each appear twice because their entries from both lists are preserved. The highlighted rows show these repeated occurrences.

The result contains six rows. These still describe four distinct people, but they preserve all six attendance entries.

SQL Queries for the Example

The queries below use the exact table names, columns, and data shown in the image. MySQL and PostgreSQL use the same syntax here.

Using UNION

To return distinct attendee ID and name pairs, place UNION between the two SELECT statements.

SELECT attendee_id, attendee_name
FROM online_attendees
UNION
SELECT attendee_id, attendee_name
FROM onsite_attendees
ORDER BY attendee_id, attendee_name;

Output

attendee_id

attendee_name

101

Asha

102

Kabir

103

Meera

104

Ravi

Both selected values match in the repeated Kabir rows and in the repeated Meera rows. UNION therefore keeps one copy of each pair.

Using UNION ALL

To preserve every entry, use UNION ALL between the SELECT statements.

SELECT attendee_id, attendee_name
FROM online_attendees
UNION ALL
SELECT attendee_id, attendee_name
FROM onsite_attendees
ORDER BY attendee_id, attendee_name;

Output

attendee_id

attendee_name

101

Asha

102

Kabir

102

Kabir

103

Meera

103

Meera

104

Ravi

Every input row contributes a result row, giving 3 + 3 = 6 rows. The final ORDER BY produces the same ordering shown in the image.

Neither query changes the original tables.

Key Differences and When to Use Each

Comparison

UNION

UNION ALL

Repeated rows

Keeps one copy of each distinct row

Keeps every occurrence

Duplicate elimination

Required

Skipped

Result in this example

Four distinct attendee rows

Six attendance entries

Suitable use

A distinct list of selected rows

A complete collection of entries

Processing work

May require extra work to remove duplicates

Usually requires less work

For the event’s combined participant list, UNION produces the required four rows.

For a report that must preserve each online and onsite attendance entry, UNION ALL keeps the information needed. Removing repeated entries could otherwise change counts or totals calculated from the combined result.

UNION ALL usually performs less work because it skips duplicate elimination. Actual execution time depends on the data and execution plan. Choose the operator that gives the correct result before considering performance.

Summary

UNION and UNION ALL combine compatible query results, but they handle repeated rows differently. In the attendance example, UNION returns four distinct attendee rows, while UNION ALL preserves all six entries. Use UNION when distinct rows are required and UNION ALL when every occurrence matters. Always match the selected columns and specify ordering when needed.

CS Core

Read Similar Blogs

Comments0