One-to-one relationship design in SQL - Time & Space Complexity

Time Complexity: One-to-one relationship design

O(n)

Understanding Time Complexity

When working with one-to-one relationships in databases, it's important to understand how the time to get data grows as the data grows.

We want to know how the cost of joining two tables changes when the number of rows increases.

Scenario Under Consideration

Analyze the time complexity of the following SQL query joining two tables with a one-to-one relationship.

SELECT a.id, a.name, b.details
FROM TableA a
JOIN TableB b ON a.id = b.a_id
WHERE a.status = 'active';
    

This query fetches active records from TableA and their matching details from TableB using a one-to-one link.

Identify Repeating Operations

Look for repeated actions that affect performance.

• Primary operation: Scanning and matching rows from TableA and TableB.
• How many times: Once for each row in TableA that meets the condition.

How Execution Grows With Input

As the number of active rows in TableA grows, the work to find matching rows in TableB grows too.

Input Size (n)	Approx. Operations
10	About 10 row matches
100	About 100 row matches
1000	About 1000 row matches

Pattern observation: The number of operations grows roughly in direct proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly with the number of rows in TableA.

Common Mistake

[X] Wrong: "Joining two tables always doubles the work, so it's O(n²)."

[OK] Correct: Because each row in TableA matches exactly one row in TableB, the join only processes each row once, so the work grows linearly, not squared.

Interview Connect

Understanding how joins behave with one-to-one relationships helps you explain query performance clearly and confidently.

Self-Check

"What if TableB had multiple matching rows per TableA row (one-to-many)? How would the time complexity change?"