Find Customer Referee

Difficulty: Easy | Acceptance: 72.80% | Paid: No Topics: Database

Table: Customer

+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | referee_id | int | +-------------+---------+ In SQL, id is the primary key column for this table. Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.

Find the names of all the customers that are not referred by the customer with id = 2.

Return the result table in any order.

The result format is in the following example.

• Examples
• Constraints
• Approach 1: Using IS NULL and !=
• Approach 2: Using COALESCE
• Approach 3: Using NOT IN

Examples

Example 1:

Input: Customer table: +----+------+------------+ | id | name | referee_id | +----+------+------------+ | 1 | Will | NULL | | 2 | Jane | NULL | | 3 | Alex | 2 | | 4 | Bill | NULL | | 5 | Zack | 1 | | 6 | Mark | 2 | +----+------+------------+ Output: +------+ | name | +------+ | Will | | Jane | | Bill | | Zack | +------+

Constraints

1 <= Customer.id <= 1000
Customer.name is a non-empty string of length 1 to 100.
referee_id is either NULL or an integer between 1 and 1000.

Approach 1: Using IS NULL and !=

Intuition We need to find customers who either have no referee (referee_id is NULL) or have a referee that is not customer 2.

Steps

• Select name from Customer table
• Filter where referee_id is NULL OR referee_id is not equal to 2

# Write your MySQL query statement below
SELECT name FROM Customer WHERE referee_id IS NULL OR referee_id != 2

Complexity

• Time: O(n) where n is the number of rows in Customer table
• Space: O(k) where k is the number of results
• Notes: This is the most straightforward approach

Approach 2: Using COALESCE

Intuition COALESCE returns the first non-NULL value. We can use it to convert NULL referee_id to a value that’s not 2.

Steps

• Use COALESCE to convert NULL referee_id to 0 (or any value not equal to 2)
• Filter where the result is not equal to 2

# Write your MySQL query statement below
SELECT name FROM Customer WHERE COALESCE(referee_id, 0) != 2

Complexity

• Time: O(n) where n is the number of rows in Customer table
• Space: O(k) where k is the number of results
• Notes: COALESCE is a standard SQL function that handles NULL values elegantly

Approach 3: Using NOT IN

Intuition We can find customers whose referee_id is NOT IN the list [2], but we need to handle NULL values carefully.

Steps

• Select names where referee_id is NOT IN (2)
• This automatically excludes NULL values because NULL comparisons in SQL return UNKNOWN

# Write your MySQL query statement below
SELECT name FROM Customer WHERE referee_id IS NULL OR referee_id NOT IN (2)

Complexity

• Time: O(n) where n is the number of rows in Customer table
• Space: O(k) where k is the number of results
• Notes: NOT IN with NULL values can be tricky - NULL NOT IN (2) returns UNKNOWN, not TRUE