Join Mechanics and Ranking Window Functions: How SQL Handles Duplicate Keys and Ordering

Understanding how SQL processes duplicate keys during table joins and how window functions handle ties is essential for data engineering and database interviews. This chapter breaks down the exact mechanics of null-value join behaviors, cartesian matching principles, and the distinct operational differences between RANK(), DENSE_RANK(), and ROW_NUMBER().

1. Why Do We Need Join Mechanics & Ranking Functions?

In real-world relational databases, datasets are rarely clean. Tables frequently contain duplicate keys, missing attributes, and NULL values. When joining tables or generating analytical reports (such as salary leaderboards or top-N employee lists), naive queries often fail or produce unintended rows. Knowing the underlying mechanics prevents silent data expansion and incorrect rankings.

2. Core Mental Model: The Unmatchable Nature of NULL

When database engines perform joins (such as inner or outer joins), they match rows based on equality conditions (ON tableA.key = tableB.key).

• The Golden Rule: In SQL logic, NULL represents an unknown or missing value. Therefore, NULL is never equal to NULL.
• Real-Life Analogy: Think of NULL as an unknown ballot box or an unlisted phone number. You cannot verify if two unlisted numbers belong to the same person, so the database refuses to match them together. Each NULL in Table A remains isolated from every NULL in Table B.

3. Step-by-Step Evolution of Join Expansion with NULLs

Let's examine how dataset sizes expand when NULL values are introduced.

1. Initial Clean State: Suppose a clean join operation between two tables produces exactly 12 matching records.
2. Introduction of NULLs: We insert a row containing NULL into Table A and a row containing NULL into Table B.
3. The Evaluation: Because NULL = NULL evaluates to UNKNOWN (false in join predicates), the join engine cannot pair them together.
4. The Result:
   • The original 12 matching records remain intact.
   • The NULL record from Table A fails to find a partner and is treated as a non-matching row.
   • The NULL record from Table B likewise fails to find a partner.
   • Total resulting records = $12 + 1 (\text{Table A null}) + 1 (\text{Table B null}) =$ 14 records.

Note: Always remember that non-matching records and NULL keys bypass standard equality checks, creating distinct independent rows rather than collapsing into a single combined row.

4. Introduction to Ranking Window Functions

When moving beyond simple joins, SQL interviewers frequently test analytical ranking functions. Given an employee table, our goal is to assign a sequential ranking based on employee salaries in descending order (highest salary getting rank 1).

SELECT 
    employee_id, 
    salary, 
    RANK() OVER (ORDER BY salary DESC) AS rn
FROM employees;

• Why DESC matters: By default, SQL's ORDER BY clause sorts data in ascending (ASC) order. Without DESC, the employee with the lowest salary receives rank 1. Adding DESC ensures the highest salary occupies the top position.

5. Comparative Breakdown of Ranking Functions

SQL provides three core ranking window functions. While they look similar on initial inspection, they handle duplicate values (ties) and gaps in radically different ways.

6. Deep Dive: RANK() Mechanics

The RANK() function assigns the same rank to rows with identical values in the ORDER BY clause, but it skips subsequent rank numbers proportional to the number of ties.

Code Demonstration (RANK)

SELECT 
    name, 
    salary, 
    RANK() OVER (ORDER BY salary DESC) AS rn
FROM employees;

Execution Behavior

• Rank 1: Salary 15,000 (1 person) $\rightarrow$ Rank: 1
• Rank 2: Salary 12,000 (2 people tied) $\rightarrow$ Ranks: 2, 2
• Rank 4: Salary 10,000 (2 people tied) $\rightarrow$ Ranks: 4, 4 (Notice rank 3 is skipped because two people occupied rank 2)
• Rank 6: Salary 8,000 (1 person) $\rightarrow$ Rank: 6 (Skipped rank 5)

7. Deep Dive: DENSE_RANK() Mechanics

The word dense means compact. DENSE_RANK() assigns identical ranks to tied values just like RANK(), but it does not leave gaps in the sequence.

Code Demonstration (DENSE_RANK)

SELECT 
    name, 
    salary, 
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rn
FROM employees;

Execution Behavior

• Rank 1: Salary 15,000 $\rightarrow$ Rank: 1
• Rank 2: Salary 12,000 (Tied) $\rightarrow$ Ranks: 2, 2
• Rank 3: Salary 10,000 (Tied) $\rightarrow$ Ranks: 3, 3 (No gap! Immediately follows 2)
• Rank 4: Salary 8,000 $\rightarrow$ Rank: 4

8. Deep Dive: ROW_NUMBER() Mechanics

Unlike the previous two functions, ROW_NUMBER() never assigns duplicate values, even if two employees share the exact same salary. It produces a strict, unique sequential integer for every row.

Code Demonstration (ROW_NUMBER)

SELECT 
    name, 
    salary, 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

Execution Behavior

• Row 1: Salary 15,000 $\rightarrow$ 1
• Row 2: Salary 12,000 $\rightarrow$ 2
• Row 3: Salary 12,000 (Tie with above) $\rightarrow$ 3 (Deterministically assigned based on internal storage/index order)
• Row 4: Salary 10,000 $\rightarrow$ 4

9. Summary and Key Takeaways

• NULL Inequality: NULL values never evaluate as equal to other NULL values; joins preserve them as separate, un-matched individual records.
• Join Expansion Warning: Failing to account for NULL keys during join operations can unexpectedly inflate row counts.
• Default Sort Direction: SQL window functions (ORDER BY) default to ascending (ASC) order; explicit DESC is required for top-down descending leaderboards.
• RANK() Behavior: Assigns identical ranks to ties and skips subsequent numbers (e.g., 1, 2, 2, 4).
• DENSE_RANK() Behavior: Assigns identical ranks to ties with no gaps in the sequence (e.g., 1, 2, 2, 3).
• ROW_NUMBER() Behavior: Generates strictly unique, continuous sequence numbers (1, 2, 3, 4) without ever sharing ranks for ties.
• Interview Ready: Be prepared for trick questions involving null join cardinalities and selecting between RANK and DENSE_RANK when handling leaderboard ties.