Using Set Operators in SQL: UNION, INTERSECT, MINUS, EXCEPT

Set operators combine the results of two or more SELECT statements into a single result set. They come from set theory — union, intersection, difference — and they work by comparing whole rows across the two sides.

SELECT customer_name FROM customers
UNION
SELECT customer_name FROM archived_customers;

That merges the two customer lists and removes duplicates. Every unique name that appears in either table shows up once.

The five set operators are:

• UNION — combine both sides, remove duplicates
• UNION ALL — combine both sides, keep duplicates
• INTERSECT — rows that appear in both sides
• MINUS — rows in the first side that are not in the second (Oracle)
• EXCEPT — same as MINUS (SQL Server, PostgreSQL)

Two rules apply to all of them. Both sides must return the same number of columns, and the matching columns must have compatible data types. Break either rule and the query fails — no partial results.

The difference is whether duplicates are removed. UNION removes them; UNION ALL keeps them.

-- UNION: combines and removes duplicates
SELECT customer_name FROM customers
UNION
SELECT customer_name FROM archived_customers;

-- UNION ALL: combines and keeps duplicates
SELECT customer_name FROM customers
UNION ALL
SELECT customer_name FROM archived_customers;

If Alice Corp appears in both tables, the UNION version returns her once. The UNION ALL version returns her twice — once from each side.

Performance follows directly from that. UNION has to sort or hash both result sets to find the duplicates. UNION ALL just concatenates them. On large tables, UNION can be several times slower than UNION ALL for the same inputs.

My rule of thumb: default to UNION ALL, and switch to UNION only when you specifically need de-duplication. Most of the time, the two sides cannot overlap and de-duplication is wasted work.

INTERSECT returns only the rows that appear in both result sets. It is the set-theory intersection — the overlap.

SELECT customer_id FROM customers
INTERSECT
SELECT customer_id FROM archived_customers;

That returns only the customer ids that appear in both tables. On our data, that is C001 and C002. Every other customer is excluded, either because they are only in the live table or only in the archive.

INTERSECT removes duplicates automatically. If C001 appeared three times in customers and twice in archived_customers, the result would still show C001 once. That makes INTERSECT useful for overlap analysis where the fact of the overlap matters more than the count.

A common use case: comparing two systems and finding the records they agree on. Or finding customers who purchased in two different time periods, or products that appear in multiple categories. Anywhere you want the overlap, INTERSECT is the natural fit.

EXCEPT returns the rows from the first result set that do not appear in the second. It is the set difference — what is in one but not the other.

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM archived_customers;

That returns every customer id in the live table that is not in the archive. On our data, that is C003, C004, C005, C006, C007 — the customers added since the archive was taken.

The direction matters. Swap the two SELECT statements and you get the opposite question answered:

SELECT customer_id FROM archived_customers
EXCEPT
SELECT customer_id FROM customers;

That returns C008 and C009 — customers who are in the archive but not in the live table. These are the ones who have been retired or deleted.

EXCEPT is the SQL Server and PostgreSQL name for this operation. On Oracle, the same operation is called MINUS. Some databases accept both spellings; the behaviour is identical.

EXCEPT removes duplicates automatically, like the other set operators. A value that appears five times on the first side and never on the second appears once in the result.

UNION removes them. When the same row appears on both sides of a UNION, only one copy survives in the result.

SELECT customer_name FROM customers
UNION
SELECT customer_name FROM archived_customers;

If Alice Corp appears in both tables, that query returns her once. Same for any other name that happens to be in both. The result is the distinct set of names across the two tables.

Under the hood, the database implements this by sorting (or hashing) the combined result and then keeping only the first occurrence of each unique row. Two rows are considered duplicates only if every column in the row matches — the same customer id, the same name, the same region, and so on.

That is why UNION is slower than UNION ALL. The sort-and-compare step has real cost, and it scales with the size of the combined result set.

If you know the two sides cannot overlap, or you do not care about duplicates, UNION ALL does the same concatenation without the de-duplication step. It is always at least as fast, and often much faster.

INTERSECT returns the rows that appear in both result sets. Anything that appears on only one side is dropped. That makes it the natural tool for finding overlap.

SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;

That returns every customer id that placed at least one order in both 2023 and 2024. These are the repeat customers — the ones who came back.

The output is deduplicated. A customer who ordered five times in 2023 and three times in 2024 appears once. That is usually what you want for overlap analysis — the fact of the overlap matters more than how many orders were involved.

Compare to an alternative with an INNER JOIN:

SELECT DISTINCT o23.customer_id
FROM   orders_2023 o23
JOIN   orders_2024 o24 ON o23.customer_id = o24.customer_id;

That returns the same rows but you have to add DISTINCT yourself, because the join produces one row per matching pair. INTERSECT does that automatically, which is one reason it is often the cleaner choice for overlap.

The two approaches differ when there are other columns involved. INTERSECT compares the full row, so if you select three columns and only want the overlap on two of them, INTERSECT will not do what you expect. That is where the join is more flexible.

Set operators combine rows from two queries vertically — the result is taller. Joins combine columns from two tables horizontally — the result is wider.

-- JOIN: wider result
SELECT c.customer_name, o.order_id, o.order_amount
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id;

-- UNION: taller result
SELECT customer_id, customer_name FROM customers
UNION
SELECT customer_id, customer_name FROM archived_customers;

The JOIN returns one row per order with the customer name attached — three columns, seven rows. The UNION returns one row per customer across both tables — two columns, nine rows.

The matching rule is also different. A JOIN can match on any condition — equality, range, inequality, complex boolean logic. A set operator only compares whole rows for equality. You cannot say "combine rows where column A is greater than column B" with a set operator.

Duplicates are handled differently too. A JOIN keeps every matching pair, so a customer with three orders appears three times in the joined result. A UNION removes duplicates by default, so a customer who appears in both source tables shows up once.

Which to use depends on the question. "Show me orders with their customer names" is a join. "Show me customers who appear in both systems" is a set operator.

No. Every SELECT involved in a set operation must return exactly the same number of columns. If they do not, the query fails with an error.

-- This fails
SELECT customer_id, customer_name FROM customers
UNION
SELECT customer_id FROM archived_customers;
-- ERROR: each UNION query must have the same number of columns

The first query returns two columns, the second one column. The database cannot align them, so it refuses to run the statement. No partial results, no warnings — just a hard failure.

The fix depends on what you meant. If both tables really do have matching structure, just list the same columns on both sides:

SELECT customer_id, customer_name FROM customers
UNION
SELECT customer_id, customer_name FROM archived_customers;

If the two tables have different shapes, pad the shorter one with NULLs or default values:

SELECT customer_id, customer_name FROM customers
UNION
SELECT customer_id, NULL FROM archived_customers;

That gives every row two columns. The rows from the second table have NULL in the second column because there is no real value to fill in.

The column names come from the first SELECT. That means the alias on the second SELECT is ignored — anything downstream that references columns by name will see the names from the first query.

MINUS is Oracle's name for the set-difference operation. It returns rows from the first SELECT statement that are not present in the second.

SELECT customer_id FROM customers
MINUS
SELECT customer_id FROM archived_customers;

That returns every customer id in the live table that is not in the archive. On our data, that is C003 through C007 — the ones added since the archive was taken.

MINUS is equivalent to EXCEPT on other databases. SQL Server and PostgreSQL use EXCEPT; Oracle uses MINUS. The behaviour is identical. Some databases support both spellings.

Like the other set operators, MINUS de-duplicates automatically. If a value appears five times on the first side and never on the second, it appears once in the result.

Direction matters. Swapping the two SELECT statements produces a different result — the opposite question. "What is new?" versus "What has been retired?" are both useful, and the order tells the database which one you want.

In set operations, NULL is treated as equal to NULL. That is different from normal comparisons, where NULL = NULL evaluates to unknown and fails.

SELECT customer_id, credit_limit FROM customers
UNION
SELECT customer_id, credit_limit FROM archived_customers;

If Fiona Mart appears in both tables with a NULL credit limit, the UNION treats the two rows as duplicates and keeps only one. That matches most people's intuition — the two rows describe the same customer with the same (unknown) credit limit.

Same for INTERSECT. If NULL appears in the same column position on both sides, INTERSECT considers it a match and keeps the row. If one side has NULL and the other has 0, they are different rows and both survive.

This is actually helpful because it makes set operators more predictable than a hand-written equality comparison. Writing WHERE a.col = b.col would fail for NULLs, but INTERSECT does not.

If you want a different NULL handling — say NULL should be treated as distinct from another NULL, or NULL should be replaced with a value — do the substitution before the set operator:

SELECT customer_id, COALESCE(credit_limit, -1) FROM customers
UNION
SELECT customer_id, COALESCE(credit_limit, -1) FROM archived_customers;

Both sides replace NULL with -1, so the set operation compares the -1 values. Pick a sentinel that cannot naturally appear in the data.

Yes. Any SELECT statement — including a subquery — can be one side of a set operation. The subquery just has to produce the same column shape as the other side.

SELECT customer_id FROM customers
UNION
SELECT customer_id FROM (
    SELECT customer_id FROM archived_customers WHERE status = 'active'
);

The second side is a subquery that filters archived customers to just the active ones. The subquery runs first, produces a result set, and the UNION combines it with the customers side.

You can also use a subquery in the WHERE clause of a side:

SELECT customer_id FROM customers
UNION
SELECT customer_id FROM archived_customers
WHERE archived_on > (SELECT MAX(snapshot_date) FROM snapshots);

Subqueries inside a UNION are common when one side depends on a value computed from another query. The general rule is that any legal SELECT works — the only constraint is that the columns have to line up.

One thing to watch for: correlated subqueries inside a UNION side can be slow if they reference columns from the other side. The sides of a UNION are logically independent — the database does not cross-reference them during execution — so correlations that try to do that will not work.

UNION is slower because it has to remove duplicates. UNION ALL skips that step and runs significantly faster on large tables.

-- UNION: runs both queries, sorts, de-duplicates, returns
SELECT customer_id FROM customers
UNION
SELECT customer_id FROM archived_customers;

-- UNION ALL: runs both queries, concatenates, returns
SELECT customer_id FROM customers
UNION ALL
SELECT customer_id FROM archived_customers;

The extra cost in UNION is the sort or hash operation the database uses to find duplicates. On two result sets of a thousand rows each, that is trivial. On two result sets of ten million rows each, the sort can take longer than the queries themselves.

The magnitude of the difference depends on the database's implementation. Some databases use sort-based de-duplication, which is O(n log n) on the combined result. Others use hash-based, which is roughly O(n) but with a large constant cost for building the hash table. Either way, UNION ALL is at least as fast, and often several times faster.

When performance matters, default to UNION ALL and switch to UNION only when you specifically need duplicates removed. That decision alone can save seconds on a large report.

If you do need UNION's de-duplication and the query is slow, the usual fix is to filter each side with a WHERE clause before the UNION — smaller inputs mean faster sorting. Sometimes a different formulation entirely, like a NOT EXISTS subquery, ends up faster than the UNION.

Put a single ORDER BY at the very end, after the last SELECT statement. It sorts the combined result, not the individual sides.

SELECT customer_name FROM customers
UNION
SELECT customer_name FROM archived_customers
ORDER BY customer_name;

That returns every unique customer name sorted alphabetically. The ORDER BY applies to the whole union, not to the customers side or the archive side.

Putting an ORDER BY inside the individual SELECT statements does not work — most databases reject it with a syntax error, and the ones that accept it usually ignore it.

-- This fails
SELECT customer_name FROM customers ORDER BY customer_name
UNION
SELECT customer_name FROM archived_customers;

The ORDER BY has to come after the last SELECT. Once it is there, you can order by column name, column position, or an alias from the first SELECT:

SELECT customer_id, customer_name FROM customers
UNION
SELECT customer_id, customer_name FROM archived_customers
ORDER BY 2;   -- sort by the second column

SELECT customer_name AS name FROM customers
UNION
SELECT customer_name FROM archived_customers
ORDER BY name;   -- sort by the alias from the first SELECT

Positional ordering (ORDER BY 2) is fragile — insert a new column in the SELECT and the position reference breaks. Named ordering is more readable and more robust.

Four limitations come up often enough to be worth knowing.

Column count and types must match exactly. You cannot combine a query that returns three columns with one that returns two. You cannot combine a numeric column with a text column without an explicit cast. That restriction means set operators work best when the two sides genuinely have the same shape.

Duplicates are removed by default. Every set operator except UNION ALL de-duplicates, and that de-duplication has a real performance cost. Forgetting this and using UNION when UNION ALL would do is one of the most common slow-query causes.

Whole-row equality only. Set operators compare entire rows. You cannot say "combine rows where column A matches and column B is greater than column C" — that kind of logic belongs in a join, not a set operation. This makes set operators unsuitable for any matching rule that is not pure equality.

Limited to specific use cases. Set operators work best for consolidation, overlap analysis, and set difference. For anything more nuanced — reports that need columns from multiple tables, comparisons that involve ranges, queries that need to match on partial keys — a join is almost always the right tool.

None of these are reasons to avoid set operators. They are reasons to reach for them when they fit the problem, and reach for joins when they do not.

Yes. Each side of a set operation can have its own aggregate functions and GROUP BY. The set operator then combines the aggregated results.

SELECT region,
       SUM(order_amount) AS total
FROM   orders
GROUP BY region

UNION ALL

SELECT region,
       SUM(credit_limit) AS total
FROM   customers
GROUP BY region;

Each side produces one row per region. The UNION ALL stacks them. The result has one row per (region, source) combination — six rows for three regions with two sources each.

GROUP BY before the set operator is important. Without it, the individual rows would appear and the set operator would try to de-duplicate across sides — which would be wrong because the numbers mean different things. GROUP BY ensures each side produces a single row per group, and the set operator combines cleanly.

You can also aggregate across the combined result by wrapping the union in a subquery:

SELECT region, SUM(total)
FROM  (
    SELECT region, SUM(order_amount) AS total FROM orders GROUP BY region
    UNION ALL
    SELECT region, SUM(credit_limit) FROM customers GROUP BY region
) combined
GROUP BY region;

That sums both sources per region. The subquery produces the combined result; the outer query aggregates it further. Anything you can do with a normal result set, you can do with the result of a set operation.

If the columns do not match — either in count or in type — the database raises an error and the query fails.

-- Column count mismatch
SELECT customer_id, customer_name FROM customers
UNION
SELECT customer_id FROM archived_customers;
-- ERROR: each UNION query must have the same number of columns

-- Data type mismatch
SELECT customer_id FROM customers
UNION
SELECT 123 FROM dual;
-- ERROR: expression must have same datatype as corresponding expression

Both failures are caught at parse time, before any rows are processed. No partial results, no warnings.

The fix depends on what is wrong. For a column count mismatch, either add columns to the shorter query or drop columns from the longer one:

-- Pad the shorter side with NULL
SELECT customer_id, customer_name FROM customers
UNION
SELECT customer_id, NULL FROM archived_customers;

For a type mismatch, cast one side to match the other:

SELECT CAST(customer_id AS VARCHAR2(20)) FROM customers
UNION
SELECT CAST(order_id AS VARCHAR2(20)) FROM orders;

Both sides now produce strings of the same length, and the UNION succeeds. The resulting column uses whatever name the first SELECT gave it.

If you are combining tables that were never designed to line up, this is a signal. Sometimes the right answer is to build an intermediate query or a view that presents the data in a consistent shape, and use that as the source of the set operation.

Set operators require compatible data types on corresponding columns. Two columns in the same position must either be the same type, or types the database can implicitly convert.

Compatible combinations vary by database, but the general rules are:

• Numeric types — INTEGER, NUMBER, DECIMAL, and FLOAT are usually compatible with each other. The database promotes them to the widest type in the pair.
• String types — VARCHAR and CHAR are compatible if the lengths work out. VARCHAR2 and VARCHAR are usually the same on databases that have both.
• Date and timestamp — usually compatible with each other. A date can be promoted to a timestamp.
• Numbers and strings — not compatible. The database will not implicitly convert '123' to 123 in a set operation.
• Strings and dates — not compatible. A string that looks like a date is still a string.

Where the types are not compatible, cast explicitly:

SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS order_key FROM orders
UNION
SELECT order_key FROM legacy_orders;

That turns a date into a string on the first side so it matches the string column on the second side.

Explicit casts are better than relying on implicit conversion even when the database allows it. Implicit conversion rules vary between databases, and a query that works on Oracle may fail on PostgreSQL because the implicit conversion there is stricter. Being explicit makes the query portable.

UNION, INTERSECT, MINUS, and EXCEPT de-duplicate automatically. Adding an explicit DISTINCT inside the SELECT statements does not change the result — the set operator already does that.

-- These two queries return the same result
SELECT customer_id FROM customers
UNION
SELECT customer_id FROM archived_customers;

SELECT DISTINCT customer_id FROM customers
UNION
SELECT DISTINCT customer_id FROM archived_customers;

The set operator treats its inputs as sets, and a set by definition has no duplicates. The explicit DISTINCT inside is redundant.

Where this matters: performance. Adding an unnecessary DISTINCT to each side forces the database to de-duplicate twice — once inside each SELECT, and again at the set operator. On large tables, that extra de-duplication can cost real time.

The one exception is UNION ALL. That operator does not de-duplicate, so a DISTINCT inside one side has a visible effect:

-- These two queries return DIFFERENT results
SELECT customer_id FROM customers
UNION ALL
SELECT customer_id FROM archived_customers;

SELECT DISTINCT customer_id FROM customers
UNION ALL
SELECT customer_id FROM archived_customers;

In the first, if a customer appears three times in customers, all three rows come through. In the second, the DISTINCT collapses them to one before the UNION ALL runs, so only one row comes through from that side.

As a rule, do not add DISTINCT inside a set operation unless you specifically need to de-duplicate one side before the operator runs. In UNION, INTERSECT, MINUS, and EXCEPT, the operator already handles it.

Set operators with aggregate functions let you combine summaries from different sources. Each side computes its own aggregates, and the set operator stacks the results into one report.

SELECT department_id, SUM(salary) AS total_salary
FROM   employees_current
GROUP BY department_id

UNION ALL

SELECT department_id, SUM(salary)
FROM   employees_archived
GROUP BY department_id;

Each side produces one row per department. The UNION ALL stacks them. If a department exists on both sides, it appears twice — once for current employees, once for archived. A reader can tell which is which by looking at the source or by adding a source label column.

Adding a source label makes the output clearer:

SELECT department_id, SUM(salary) AS total_salary, 'Current' AS source
FROM   employees_current
GROUP BY department_id

UNION ALL

SELECT department_id, SUM(salary), 'Archived'
FROM   employees_archived
GROUP BY department_id;

Now every row carries a label. That is a common pattern for reports that need to distinguish between sources — current versus archived, internal versus external, this year versus last year.

If you want a single row per department with the combined total across both sources, wrap the union in a subquery:

SELECT department_id, SUM(total_salary)
FROM  (
    SELECT department_id, SUM(salary) AS total_salary FROM employees_current GROUP BY department_id
    UNION ALL
    SELECT department_id, SUM(salary) FROM employees_archived GROUP BY department_id
) combined
GROUP BY department_id;

That collapses both sides into one row per department. The subquery does the combining; the outer query does the final aggregation.

Not directly. UNION combines result sets from SELECT statements. INSERT, UPDATE, and DELETE do not produce result sets — they modify data.

But you can use a SELECT with UNION as the source for an INSERT:

INSERT INTO all_employees (employee_id, name)
SELECT employee_id, name FROM employees_current
UNION
SELECT employee_id, name FROM employees_archived;

The UNION combines two sources and de-duplicates, and the INSERT writes the result into the target table. That is a common pattern for consolidating data from multiple sources into a single table.

For UPDATE and DELETE, the pattern is different. You can filter rows based on a set operation by putting it in a subquery:

DELETE FROM customers
WHERE  customer_id IN (
    SELECT customer_id FROM customers
    MINUS
    SELECT customer_id FROM active_customers
);

That deletes customers whose ids are not in the active list. The MINUS produces the set of ids to remove, and the outer DELETE acts on it.

Some databases also support MERGE, which is a statement that does INSERT, UPDATE, and DELETE in one pass based on a comparison of two sources. MERGE is not a set operator, but it plays a similar role for synchronisation scenarios.

The rule to remember: UNION is a way of combining result sets. Any statement that consumes a result set — INSERT ... SELECT is the main one — can use it. Statements that modify rows directly cannot.