Find Customer Referee | Leetcode 584 | Crack SQL Interviews in 50 Qs

1. Why Do We Need This? (Problem Statement & Status Quo)

When querying relational databases, we frequently encounter columns that contain missing or unknown data represented as NULL. Standard comparison operators in SQL (such as $=$, $\neq$, $<$, $>$) are designed for concrete values. When applied to NULL, they evaluate to an unknown or false truth state rather than true or false.

In this chapter, we explore LeetCode 584: Find Customer Referee, a classic SQL interview problem that exposes the nuances of handling NULL values and three-valued logic in relational database management systems.

2. Core Schema & Data Structure

The problem provides a single table named Customer with three columns representing customer attributes and referral relationships.

Table Schema: Customer

Sample Data & Visualization

3. The Objective: What Do We Need to Find?

Our task is to find the names of the customers who are not referred by the customer with id = 2.

Based on the sample data:

• Alex (id: 3) has referee_id = 2 (Excluded).
• Mark (id: 6) has referee_id = 2 (Excluded).
• Will, Jane, Bill, and Zack have either NULL or other IDs (1) as their referee_id. Therefore, they should be included in our result set.

4. The Naive Approach and Why It Fails

An inexperienced developer might write a straightforward query using standard inequality operators to filter out records where referee_id equals 2.

Naive SQL Query

SELECT name 
FROM Customer 
WHERE referee_id != 2;

Why This Fails in Production

When executing this query against the sample dataset, the expected output should include Will, Jane, Bill, and Zack. However, the naive query only returns Zack.

Note on Three-Valued Logic: In SQL, NULL represents the absence of a value. Any arithmetic or comparison operation involving NULL (e.g., NULL != 2) does not evaluate to TRUE or FALSE; it evaluates to UNKNOWN. Because the WHERE clause strictly filters for rows that evaluate to TRUE, rows with NULL in the referee_id column are silently dropped by the database engine.

5. Step-by-Step Evolution: Solution 1 (Handling NULL Explicitly)

To capture customers who were not referred by 2 plus those who were not referred by anyone at all (NULL), we must combine our inequality condition with an explicit check for NULL using the IS NULL operator, joined by the OR logical operator.

Improved SQL Query

SELECT name 
FROM Customer 
WHERE referee_id != 2 
   OR referee_id IS NULL;

Execution Flow Walkthrough

6. Alternative Evolution: Solution 2 (Using COALESCE / IFNULL)

Another professional technique to handle NULL values in SQL comparisons is to transform the NULL value into a safe default placeholder before performing the evaluation.

In MySQL, we can use the IFNULL() function (or standard SQL COALESCE()) to replace NULL with a number that will never match our exclusion target (e.g., 0).

Optimized SQL Query using IFNULL

SELECT name 
FROM Customer 
WHERE IFNULL(referee_id, 0) != 2;

How IFNULL() Resolves the Problem

1. First Parameter: Evaluates referee_id.
2. Second Parameter: Specifies the fallback value (0) if the first parameter is NULL.
3. For Will (referee_id = NULL), IFNULL(NULL, 0) converts the value to 0.
4. The expression checks 0 != 2, which evaluates to TRUE. Thus, Will is included in the output.

7. Comparison of Approaches

Summary and Key Takeaways

• The Nature of NULL: NULL in SQL represents missing or unknown data and is never equal to or different from any concrete value using standard comparison operators.
• Three-Valued Logic: Comparisons involving NULL evaluate to UNKNOWN, causing standard WHERE filters to drop those rows.
• Explicit IS NULL Checks: Always account for missing data explicitly by pairing inequality checks with OR column IS NULL.
• Value Substitution: Functions like IFNULL() or COALESCE() provide a clean pattern to convert NULL into a safe default value during comparison.
• Interview Readiness: Recognizing edge cases involving NULL values is a primary differentiator evaluated by interviewers in SQL coding rounds.