Definition
UNION– Combines the rows of two queries and removes duplicate rows, requiring a deduplication sort or hash over the combined result.UNION ALL– Combines the rows of two queries keeping all duplicates, with no deduplication step, making it noticeably faster.INTERSECT– Returns only the rows that appear in both queries (the common rows), also removing duplicates.EXCEPT– Returns rows in the first query that are not in the second, calledMINUSin Oracle.- Compatibility requirement – Both queries must have the same number of columns, in the same order, with compatible data types.
- NULL handling – In set operators, two NULLs are treated as equal for matching, unlike normal
=where NULL never equals NULL.
At National Bank of India, one list contains customers who hold a savings account and another contains customers who hold a credit card. UNION gives everyone who holds either product, listing each person once even if they hold both. UNION ALL glues the two lists together as-is, so someone holding both appears twice. INTERSECT gives only the customers who hold both a savings account and a credit card – the overlap.
Understanding the Concepts
What is UNION?
UNION combines the result sets of two queries and removes duplicate rows. To remove duplicates the database must compare all rows, which typically means a sort or hash operation over the combined result.
-- UNION: all customers who hold either a savings account or a credit card (deduplicated)
SELECT customer_id, full_name FROM savings_account_holders
UNION
SELECT customer_id, full_name FROM credit_card_holders
ORDER BY full_name;
Expected output:
customer_id | full_name
-------------+--------------
1001 | Pankaj Gupta
1002 | Priya Sharma
1003 | Rajesh Kumar
-- Each person appears once, even if they hold both products
What is UNION ALL?
UNION ALL combines two result sets keeping all duplicates. It skips the deduplication step entirely, making it faster and lighter.
-- UNION ALL: combine this year and last year's transactions (no overlap possible)
SELECT txn_id, account_id, amount, txn_date FROM transactions_2024
UNION ALL
SELECT txn_id, account_id, amount, txn_date FROM transactions_2023
ORDER BY txn_date DESC;
Expected output:
txn_id | account_id | amount | txn_date
---------+------------+-----------+------------
TXN-501 | SAV2001 | 50000.00 | 2024-12-15
TXN-500 | CUR3001 | 75000.00 | 2024-12-14
...
TXN-001 | SAV2001 | 10000.00 | 2023-01-02
-- All rows from both tables, no deduplication needed
What is INTERSECT?
INTERSECT returns only the rows that appear in both result sets. It answers questions of the form "who is in both groups."
-- INTERSECT: customers who hold BOTH a savings account AND a credit card
SELECT customer_id, full_name FROM savings_account_holders
INTERSECT
SELECT customer_id, full_name FROM credit_card_holders;
Expected output:
customer_id | full_name
-------------+--------------
1002 | Priya Sharma
-- Only Priya holds both products
What is EXCEPT (MINUS)?
EXCEPT returns rows in the first query that are not in the second. Oracle calls it MINUS. It answers "who is in the first group but not the second."
-- EXCEPT: savings account holders who do NOT have a credit card
SELECT customer_id, full_name FROM savings_account_holders
EXCEPT
SELECT customer_id, full_name FROM credit_card_holders;
Expected output:
customer_id | full_name
-------------+--------------
1003 | Rajesh Kumar
-- Rajesh has a savings account but no credit card
How are UNION, UNION ALL, INTERSECT, and EXCEPT different?
| Operator | Returns | Deduplication | Speed | Use case |
|---|---|---|---|---|
| UNION | All distinct rows from both | Yes (sort/hash) | Slower | Deduplicated combined list |
| UNION ALL | All rows including duplicates | No | Fastest | Stacking non-overlapping partitions |
| INTERSECT | Rows in both queries | Yes | Medium | "Who is in both groups" |
| EXCEPT | Rows in first but not second | Yes | Medium | "Who is in first but not second" |
package com.bank.service;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class CrossSellService {
// Find customers eligible for cross-sell: have savings but no credit card
public List<String> findCrossSellTargets(Connection conn) throws SQLException {
String sql = """
SELECT customer_id, full_name FROM savings_account_holders
EXCEPT
SELECT customer_id, full_name FROM credit_card_holders
""";
List<String> targets = new ArrayList<>();
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
targets.add(rs.getString("customer_id") + ": " + rs.getString("full_name"));
}
}
return targets;
}
}
What are the rules for set operators?
All set operators require the two queries to be column-compatible: the same number of columns, in the same order, with compatible data types. The column names of the final result come from the first query. They treat whole rows as the unit of comparison.
-- WRONG: different number of columns
SELECT customer_id, full_name FROM savings_account_holders
UNION
SELECT customer_id FROM credit_card_holders;
-- ERROR: each UNION query must have the same number of columns
-- CORRECT: matching columns
SELECT customer_id, full_name FROM savings_account_holders
UNION
SELECT customer_id, full_name FROM credit_card_holders;
What about NULL handling in set operators?
In set operators, two NULLs are treated as equal for the purpose of matching rows. This is the opposite of how NULL behaves in a normal equality comparison, where NULL = NULL is UNKNOWN, not TRUE.
-- NULL handling in set operators
SELECT 'Rajesh' AS name, NULL AS branch
UNION
SELECT 'Rajesh' AS name, NULL AS branch;
-- Returns 1 row: the two NULLs match, so the duplicate is removed
-- Compare with normal equality
SELECT * FROM accounts WHERE branch = NULL;
-- Returns 0 rows: NULL = NULL is UNKNOWN, not TRUE
-- Must use: WHERE branch IS NULL
When should you use UNION ALL instead of UNION?
Default to UNION ALL and only use UNION when deduplication is genuinely required. People reach for UNION out of habit when UNION ALL is what they need, paying for a needless sort.
-- WRONG: using UNION when duplicates are impossible
-- Monthly tables are non-overlapping, so duplicates cannot exist
SELECT * FROM transactions_jan_2024
UNION -- needless deduplication sort on millions of rows!
SELECT * FROM transactions_feb_2024;
-- CORRECT: UNION ALL when duplicates are impossible or wanted
SELECT * FROM transactions_jan_2024
UNION ALL
SELECT * FROM transactions_feb_2024;
What about ORDER BY with set operators?
ORDER BY applies to the whole combined result and is written once at the end, not inside each query.
-- ORDER BY applies to the combined result
SELECT customer_id, full_name FROM savings_account_holders
UNION ALL
SELECT customer_id, full_name FROM credit_card_holders
ORDER BY full_name; -- sorts the entire combined result
What should a senior engineer know?
A senior engineer knows to default to UNION ALL for performance, that set operators can often be rewritten as joins or EXISTS/IN subqueries but read more clearly for genuine set logic, that NULL matching behavior in set operators differs from equality, and that ORDER BY applies once to the final result.
Visual Overview
┌─────────────────────────────────────────────────────┐
│ Set Operators Visual │
├─────────────────────────────────────────────────────┤
│ │
│ Query A Query B │
│ ┌───────┐ ┌───────┐ │
│ │ Rajesh│ │ Priya │ │
│ │ Priya │ │Pankaj │ │
│ └───────┘ └───────┘ │
│ │
│ UNION: { Rajesh, Priya, Pankaj } (dedup) │
│ UNION ALL: { Rajesh, Priya, Priya, Pankaj } (all) │
│ INTERSECT: { Priya } (both) │
│ EXCEPT: { Rajesh } (A not B) │
│ │
├─────────────────────────────────────────────────────┤
│ Savings Holders Credit Card Holders │
│ ┌──────────────┐ ┌──────────────┐ │
│ │ Rajesh │ │ Priya │ │
│ │ Priya ─────┼───┼─ Priya │ │
│ │ │ │ Pankaj │ │
│ └──────────────┘ └──────────────┘ │
│ EXCEPT │ INTERSECT │
│ = {Rajesh} │ = {Priya} │
│ │ │
│ UNION = {Rajesh, Priya, Pankaj} │
│ │
└─────────────────────────────────────────────────────┘
Key Takeaway
UNION, UNION ALL, INTERSECT, and EXCEPT are set operators that combine two queries which must have the same number of columns, in the same order, with compatible types. They compare whole rows. UNION returns all distinct rows from both queries, removing duplicates, which forces a deduplication sort or hash. UNION ALL returns every row including duplicates, with no deduplication, so it is noticeably faster. INTERSECT returns only the rows that appear in both queries, and EXCEPT returns rows in the first query that are not in the second.
The practical point to always raise is that people reach for UNION out of habit when UNION ALL is what they actually need, paying for a needless sort. Default to UNION ALL and only use UNION when deduplication is genuinely required, such as building a single mailing list with nobody listed twice. UNION ALL is correct whenever duplicates are impossible or wanted, like stacking two non-overlapping monthly transaction extracts.
A couple of senior nuances are worth stating: in set operators two NULLs are treated as equal for matching rows, which is the opposite of normal equality where NULL never equals NULL. And ORDER BY applies once to the whole combined result, not inside each query. These operators can often be rewritten as joins or EXISTS subqueries, but they read more clearly for genuine set logic.
At National Bank of India, the marketing team used UNION for a deduplicated either-product mailing list, Rajesh's data team used UNION ALL to concatenate non-overlapping monthly transaction extracts quickly, and the cross-sell team used INTERSECT to find customers holding both a savings account and a credit card.
Remember one sentence: UNION merges and deduplicates, UNION ALL merges keeping duplicates and is faster, and INTERSECT returns only the rows common to both queries.
---