SQL set operations: combining query results with UNION, UNION ALL, INTERSECT, and EXCEPT

Combining results instead of joining tables

Sometimes we need to combine information from two tables that have a similar structure. Consider two customer lists: one contains customers who bought through the online store, the other customers who bought through a physical store. Ben appears in both because he has purchased through both channels.

Suppose we want to answer questions such as which customers purchased through either channel, how many customer-channel records exist across both datasets, which customers purchased through both channels, or which purchased online but not in store. These problems do not require us to match columns from two tables side by side. Instead, we want to compare or combine complete query results. SQL provides four important set operations for this: UNION, UNION ALL, INTERSECT, and EXCEPT.

UNION ALL: combine results and keep every row

Suppose the requirement is “combine the online and store customer records and keep every record from both sources.”

SELECT customer_id, customer_name, country
FROM online_customers

UNION ALL

SELECT customer_id, customer_name, country
FROM store_customers;

All six input rows remain. Ben appears twice because there are two source records. UNION ALL does not ask whether the rows are duplicates; it simply appends the second query result to the first: query 1 rows + query 2 rows = all rows.

UNION: combine results and remove duplicate rows

Now suppose the requirement changes to “return the unique customers who purchased through either channel.” We no longer want Ben twice. Use UNION:

SELECT customer_id, customer_name, country
FROM online_customers

UNION

SELECT customer_id, customer_name, country
FROM store_customers;

The identical Ben row appears in both query results, so UNION returns it once. UNION ALL keeps duplicate rows; UNION removes duplicate rows. Neither is inherently better; the correct choice depends on whether repeated rows have meaning for the business question.

UNION silently removes meaningful rows

Suppose we want to know: how many customer-channel records exist across online and store purchases? There are 3 online + 3 store = 6 customer-channel records. Someone combines the lists with UNION and counts the result.

SELECT customer_id, customer_name, country
FROM online_customers

UNION

SELECT customer_id, customer_name, country
FROM store_customers;

The fix: use UNION ALL when duplicates are meaningful

If every source row represents a meaningful record, use UNION ALL. Now all six rows remain. The important question before choosing between the two operators is: if the exact same row appears in both query results, should it appear once or twice? If once, consider UNION; if once for every occurrence, use UNION ALL.

SELECT customer_id, customer_name, country
FROM online_customers

UNION ALL

SELECT customer_id, customer_name, country
FROM store_customers;

The queries must have compatible shapes

Set operations combine query results by column position. If each query returns customer_id then customer_name, the shapes are compatible. But unioning SELECT customer_id, customer_name with SELECT customer_name, customer_id does not work correctly as a meaningful combination: the positions now mean different things, and SQL is conceptually trying to combine customer_id with customer_name. Depending on the database and data types, this may produce an error or require type conversion, and even when the types happen to be compatible, the result can be logically wrong.

Before combining queries, verify: they return the same number of columns; corresponding columns represent the same kind of information; corresponding data types are compatible. Think positionally: query 1 column 1 corresponds to query 2 column 1, and so on.

Adding a source column

Sometimes we want to combine rows but still know where each row came from. We can add a constant value to each query:

SELECT customer_id, customer_name, country, 'online' AS source
FROM online_customers

UNION ALL

SELECT customer_id, customer_name, country, 'store' AS source
FROM store_customers;

Now Ben’s two rows are clearly different (Ben | online and Ben | store). This is useful when combining data from different systems, regions, sales channels, historical and current tables, or separate partitions and archives; it preserves the source as part of the result.

INTERSECT and EXCEPT

INTERSECT finds rows present in both results. “Which customers appear in both the online and store customer lists?” is not a stacking problem; we want the overlap between two result sets.

SELECT customer_id, customer_name, country
FROM online_customers

INTERSECT

SELECT customer_id, customer_name, country
FROM store_customers;

Ben appears in both query results. INTERSECT also compares complete rows: if Ben is 2 | Ben | UK online but 2 | Ben | USA in store, those rows are not considered identical. If the actual requirement is “which customer IDs occur in both datasets?”, select only the identifier that defines the comparison (SELECT customer_id on each side). The columns you select define what SQL compares.

EXCEPT: rows present only in the first result

“Customers who purchased online but do not appear in the store customer list” uses EXCEPT: first result minus rows also found in the second.

SELECT customer_id, customer_name, country
FROM online_customers

EXCEPT

SELECT customer_id, customer_name, country
FROM store_customers;

Ben is removed because he appears in both results. Dev and Esha are not returned because they exist only in the second query. EXCEPT is directional: online EXCEPT store asks “which customers are online-only?” (Asha, Carla), while store EXCEPT online asks “which are store-only?” (Dev, Esha). Those are different questions: A EXCEPT B is rows in A that are not in B.

ORDER BY applies to the combined result

Suppose we want the unique customers from both sources sorted alphabetically. A single ORDER BY at the end applies to the final combined result, not to each branch.

SELECT customer_id, customer_name, country
FROM online_customers

UNION

SELECT customer_id, customer_name, country
FROM store_customers

ORDER BY customer_name;

Do not assume that rows will remain in the order in which the individual queries happen to produce them. You might observe online rows first and store rows second after a UNION ALL, but that observed order is not guaranteed presentation order. If source grouping matters, add a source column and sort explicitly (ORDER BY source, customer_id). As with every SQL result, request the ordering you depend on.

When to reach for a set operation

Consider set operations when the requirement says combine rows from two similar datasets, merge current and archived records, combine customers from multiple channels, return unique rows across several sources, find values appearing in both datasets, find values appearing only in one dataset, compare two query results, or stack several similarly shaped queries into one result. Look for these common mistakes:

Pick the operator by the relationship you want

One more prediction

Requirement: return every customer record from both channels and preserve Ben twice because his presence in each channel is meaningful. Which query is correct?

-- Option A
SELECT customer_id, customer FROM online_customers
UNION
SELECT customer_id, customer FROM store_customers;

-- Option B
SELECT customer_id, customer FROM online_customers
UNION ALL
SELECT customer_id, customer FROM store_customers;

-- Option C
SELECT customer_id, customer FROM online_customers
INTERSECT
SELECT customer_id, customer FROM store_customers;

-- Option D
SELECT customer_id, customer FROM online_customers
EXCEPT
SELECT customer_id, customer FROM store_customers;

Summary

Set operations combine or compare complete query results. Use UNION ALL when every row from both results should remain, UNION when duplicate result rows should be removed, INTERSECT when you need rows present in both results, and EXCEPT when you need rows from the first result that do not appear in the second.

Next, we will learn how to place one query inside another and structure larger problems using subqueries and common table expressions.