Time Complexity: Set operation column matching rules
O(n^2)
0
0
Set operation column matching rules in SQL - Time & Space Complexity
Understanding Time Complexity
When using set operations like UNION or INTERSECT, the database matches columns from each query to combine results.
We want to understand how the time to match columns grows as the data size increases.
Scenario Under Consideration
Analyze the time complexity of this SQL using UNION.
SELECT id, name FROM employees
UNION
SELECT id, name FROM managers;
This query combines two lists of people, matching columns by position to remove duplicates.
Identify Repeating Operations
Look at what repeats as the database processes the query.
- Primary operation: Comparing rows from both queries to find duplicates.
- How many times: Once for each row in the combined result sets.
How Execution Grows With Input
As the number of rows grows, the database must compare more rows to find matches.
| Input Size (n) | Approx. Operations |
|---|---|
| 10 | About 100 comparisons |
| 100 | About 10,000 comparisons |
| 1000 | About 1,000,000 comparisons |
Pattern observation: The work grows roughly in proportion to the square of the number of rows.
Final Time Complexity
Time Complexity: O(n^2)
This means the time to match columns and combine rows grows quadratically with the total number of rows.
Common Mistake
[X] Wrong: "Matching columns in set operations takes constant time no matter how many rows there are."
[OK] Correct: The database must check each row against others to find duplicates, so more rows mean more work.
Interview Connect
Understanding how set operations scale helps you explain query performance clearly and confidently.
Self-Check
"What if the two queries had different numbers of columns? How would that affect the time complexity?"