UNION ALL & provenance

Combine compatible result sets

Lesson 18 of 20 · 12 min · Module 6: Trustworthy database features

Two queries can describe different sources of related information. Combining them is easier to interpret when each row also says where it came from.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Combine results with matching column roles
Keep each row’s source visible

The idea

UNION ALL appends compatible result sets and retains duplicates. Both SELECTs need the same number of columns in corresponding roles. UNION removes duplicate complete rows instead.

SELECT name, 'Profile' AS source FROM learners WHERE language = 'Python'
UNION ALL
SELECT name, 'Enrollment' AS source FROM learners WHERE id = 1;

01Agree on an output shape

Both queries return a name followed by a source label. Corresponding positions must mean compatible things.

02Explain why a person appears twice

Sam has a Python profile and a SQL enrollment. Those are distinct facts, and the source column makes that distinction visible.

03Understand duplicate behavior

UNION compares complete rows, not just names. In this example Sam’s labels differ, so even UNION would keep both; UNION ALL explicitly avoids deduplication altogether.

Your turn

Combine two lists with UNION ALL: Python-profile learners labeled Profile, and learners enrolled in SQL courses labeled Enrollment. Return name and source, retaining both of Sam’s distinct source rows. Sort by name, then source.

What the checks look for

Combines the two sources without losing Sam’s two roles

Try it in the editor →

Take this with you

Provenance labels help a combined result explain where each fact came from.

Go a little further

Return the source record id as a third column so identical display names remain distinguishable.