Outer Joins, Nulls, and Cartesian Products

This chapter covers advanced analytical query mechanics in SQL, focusing on the inner workings of window functions (ROW_NUMBER(), RANK(), and DENSE_RANK()). It details how database engines resolve ranking ties, the non-deterministic behavior of sorting, the implementation of multi-column tie-breakers, and the execution of localized window partitioning (PARTITION BY) to solve complex analytical problems.

1. The Tie-Breaking Dilemma in SQL Ranking

When performing analytical queries, ranking functions are used to assign sequential values to rows based on a specific ordering criteria. However, a common challenge arises when multiple rows share the exact same value in the ordering column (e.g., identical salaries).

While RANK() and DENSE_RANK() handle ties by assigning the same rank to duplicate values, ROW_NUMBER() is strictly designed to return a unique, sequential integer for every row. This introduces a critical question: On what basis does the database engine decide which row gets the lower sequence number when the ordering values are identical?

2. The Non-Deterministic Nature of ROW_NUMBER()

If you order a dataset by a non-unique column (such as salary) using ROW_NUMBER(), the database cannot differentiate between identical values based on that column alone.

-- Naive Ranking Query with Potential Non-Deterministic Ties
SELECT 
    emp_name, 
    salary,
    ROW_NUMBER() OVER(ORDER BY salary DESC) as row_num
FROM employees;

Real-Life Analogy

Imagine a physical queue at a reception desk. Two people arrive at the exact same millisecond to submit an application. Because their arrival times are identical, the receptionist cannot use "time of arrival" to decide who goes first. Instead, the receptionist randomly picks the person standing physically closer or on their left side.

The Physical Storage Factor

In a database, when a tie occurs on the ORDER BY column, the engine typically falls back on the physical storage location of the records on the disk blocks.

• Whichever record was inserted first or happens to reside in an earlier data block will be processed first.
• This behavior is non-deterministic. You cannot guarantee that the same row will receive the same row number across different query executions, database backups, restores, or migrations.

3. Deterministic Ranking via Multi-Column Ordering

To eliminate non-deterministic behavior and guarantee consistent results, you must introduce a secondary (and if necessary, tertiary) sorting column to break the tie. This is known as deterministic tie-breaking.

If we want to ensure that when salaries are identical, the older employee gets the higher rank (lower rank number), we can append employee_age DESC to the ORDER BY clause.

Code Evolution: Naive vs. Deterministic Tie-Breaking

Naive Approach (Non-Deterministic)

-- If Mudit and Aam both have a salary of 12,000, the assignment of ranks 2 and 3 is random
SELECT 
    emp_name, 
    salary, 
    employee_age,
    ROW_NUMBER() OVER(ORDER BY salary DESC) as row_num,
    RANK() OVER(ORDER BY salary DESC) as rank_val,
    DENSE_RANK() OVER(ORDER BY salary DESC) as dense_rank_val
FROM employees;

Deterministic Approach (Guaranteed Order)

-- Ties are broken deterministically using employee_age
SELECT 
    emp_name, 
    salary, 
    employee_age,
    ROW_NUMBER() OVER(ORDER BY salary DESC, employee_age DESC) as row_num,
    RANK() OVER(ORDER BY salary DESC, employee_age DESC) as rank_val,
    DENSE_RANK() OVER(ORDER BY salary DESC, employee_age DESC) as dense_rank_val
FROM employees;

4. The Equivalence Rule: When Ranking Functions Converge

An important mathematical and logical rule in SQL window functions is the Equivalence Rule:

When there are no duplicates in the combination of your ordering columns, there are no ties to resolve. Consequently, all three ranking functions will yield the exact same sequential output.

Demonstrating Equivalence with Unique Keys

Since emp_id is a primary key (and therefore unique), ordering by it guarantees that no duplicates exist.

SELECT 
    emp_id, 
    emp_name,
    ROW_NUMBER() OVER(ORDER BY emp_id DESC) as row_num,
    RANK() OVER(ORDER BY emp_id DESC) as rank_val,
    DENSE_RANK() OVER(ORDER BY emp_id DESC) as dense_rank_val
FROM employees;

Output Visualization

Because emp_id is unique, all three functions produce identical sequences:

5. Breaking Ties with Ascending vs. Descending Logic

By changing the sorting direction of the tie-breaker column, you can alter the business logic of your ranking system.

Case A: Older Employee Gets Preference (DESC)

-- Older employee gets the higher rank (lower number)
ORDER BY salary DESC, employee_age DESC

• If Mudit (Age 55, Salary 12,000) and Aam (Age 14, Salary 12,000) tie:
  • Mudit gets Rank 2
  • Aam gets Rank 3

Case B: Younger Employee Gets Preference (ASC)

-- Younger employee gets the higher rank (lower number)
ORDER BY salary DESC, employee_age ASC

• If Mudit (Age 55, Salary 12,000) and Aam (Age 14, Salary 12,000) tie:
  • Aam gets Rank 2
  • Mudit gets Rank 3

6. Introduction to Window Partitioning (PARTITION BY)

Up to this point, ranking has been applied globally across the entire table. However, real-world analytical questions often require localized rankings.

Interview Tip: Whenever an interviewer asks for a metric "department-wise", "category-wise", "region-wise", or "year-wise", it is a direct indicator that you need to use the PARTITION BY clause.

PARTITION BY divides the query result set into partitions (groups) before the window function is applied. The ranking calculations are executed within each partition, and the rank counter resets back to 1 when transitioning to a new partition.

-- Department-wise Salary Ranking
SELECT 
    dept_id,
    emp_name, 
    salary,
    ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) as dept_row_num,
    RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) as dept_rank,
    DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) as dept_dense_rank
FROM employees;

7. Step-by-Step Execution Flow of Partitioned Window Functions

The database engine processes partitioned window functions using a structured, sequential pipeline:

8. Comparative Synthesis of Ranking Functions

The table below synthesizes how ROW_NUMBER(), RANK(), and DENSE_RANK() behave under different duplicate and partitioning scenarios:

9. Summary and Key Takeaways

• The Tie-Breaking Problem: When ordering by non-unique columns, ROW_NUMBER() resolves ties non-deterministically based on physical storage location.
• Deterministic Queries: Always add a secondary unique column (like emp_id or created_at) to your ORDER BY clause to guarantee consistent ranking outputs across environments.
• The Equivalence Rule: If the ORDER BY columns contain completely unique values, ROW_NUMBER(), RANK(), and DENSE_RANK() will produce identical values.
• Ascending vs. Descending Tie-Breaks: Adjusting the sort direction of your secondary column allows you to easily implement business rules like "youngest first" (ASC) or "oldest first" (DESC).
• Partitioning Mechanics: The PARTITION BY clause segments data into logical groups. The window function evaluates each group independently, resetting the rank counter to 1 for each new partition.
• Identifying Partitioning Problems: Look for keywords like "department-wise" or "category-wise" in interview questions as immediate indicators that PARTITION BY is required.