Find Customer Referee - Video Solutions

Problem Overview: The table Customer stores each customer's name and the referee_id that referred them. The task is to return the names of customers whose referee is not customer 2. Customers with NULL referee IDs should also be included since they were not referred by anyone.

Approach 1: SQL WHERE Clause (O(n) time, O(1) space)

The most direct solution filters rows using a WHERE condition. Iterate through each row of the table and return customers where referee_id != 2 or the value is NULL. SQL comparisons with NULL return unknown, so an explicit IS NULL check is required. This approach scans the table once and applies a simple conditional filter, which most SQL engines execute efficiently with a sequential scan or index if available. For database-focused interview questions, this is the expected solution because it is simple and readable.

Approach 2: SQL NOT IN Clause (O(n) time, O(1) space)

Another option uses a NOT IN filter to exclude the specific referee value. The query returns rows where referee_id NOT IN (2) or where the value is NULL. This expresses the exclusion logic more declaratively and is useful when filtering multiple values. Internally, the database still performs a scan and comparison for each row. The complexity remains linear relative to the number of records.

Approach 3: Filtering Customers by Referee ID (O(n) time, O(1) space)

This version explicitly checks both conditions: customers whose referee_id is not equal to 2 and customers who have no referee. The query logic typically looks like referee_id IS NULL OR referee_id != 2. The key insight is understanding SQL’s three‑valued logic—NULL cannot be compared with standard equality operators. Explicitly including the IS NULL condition guarantees correct results.

Approach 4: Subquery to Exclude Referrals (O(n) time, O(1) space)

A slightly more verbose approach uses a subquery to filter out customers referred by ID 2. The outer query selects names while the subquery identifies rows that should be excluded. Although logically correct, the subquery does not provide performance benefits for this problem and can reduce readability. Subqueries become more useful in complex SQL scenarios involving joins or aggregated filters.

Recommended for interviews: The simple WHERE referee_id != 2 OR referee_id IS NULL solution is what interviewers expect. It demonstrates understanding of SQL filtering and correct handling of NULL values in relational database queries. Knowing the alternative NOT IN or subquery approach helps when the exclusion list becomes dynamic or derived from another table.