Time Complexity: UNION combining result sets
O(n log n)
0
0
UNION combining result sets in SQL - Time & Space Complexity
Understanding Time Complexity
When we combine two lists of data using UNION, we want to know how the work grows as the lists get bigger.
How does the time to get the combined list change when the input lists grow?
Scenario Under Consideration
Analyze the time complexity of the following code snippet.
SELECT column1 FROM tableA
UNION
SELECT column1 FROM tableB;
This code combines unique values from two tables into one list.
Identify Repeating Operations
Identify the loops, recursion, array traversals that repeat.
- Primary operation: Scanning each row in both tables and comparing to remove duplicates.
- How many times: Each row in tableA and tableB is checked once, then comparisons happen to find unique rows.
How Execution Grows With Input
As the number of rows in both tables grows, the work to scan and compare grows too.
| Input Size (n) | Approx. Operations |
|---|---|
| 10 | About 20 scans and comparisons |
| 100 | About 200 scans and comparisons |
| 1000 | About 2000 scans and comparisons |
Pattern observation: The work grows roughly in direct proportion to the total number of rows combined.
Final Time Complexity
Time Complexity: O(n log n)
This means the time to combine grows roughly in proportion to n log n, where n is the total number of rows.
Common Mistake
[X] Wrong: "UNION runs in constant time no matter how big the tables are."
[OK] Correct: The database must look at every row to find unique values, so more rows mean more work.
Interview Connect
Understanding how combining data grows with size helps you explain query performance clearly and confidently.
Self-Check
"What if we used UNION ALL instead of UNION? How would the time complexity change?"