The objective is to query the Customer table to retrieve the names of all individuals who were not referred by the agent with ID 2. This requires a specific approach to filtering because of how SQL treats NULL values.

In SQL, logical operations are governed by three-valued logic: TRUE, FALSE, and UNKNOWN. When a standard comparison operator (such as <> or !=) is used against a NULL value, the result is UNKNOWN, not TRUE. Therefore, a simple condition like referee_id <> 2 will not only exclude records where the ID is 2 but will also inadvertently exclude any records where the ID is NULL. To accurately capture these records, one must explicitly check for NULL using the IS NULL predicate.

Below are several methods to correctly construct this query.

Method 1: Explicit Logical OR

The most standard ANSI SQL approach is to explicitly state that the referee ID must either be diffferent from 2 or be NULL.

SELECT name
FROM Customer
WHERE referee_id <> 2 OR referee_id IS NULL;

Method 2: Using the COALESCE Function

The COALESCE function returns the first non-NULL value from a list of arguments. By substituting NULL values with a placeholder number that does not exist in the dataset (e.g., -1 or 0), we can simplify the comparison logic to a single condition.

SELECT name
FROM Customer
WHERE COALESCE(referee_id, -1) <> 2;

In this example, if referee_id is NULL, it is treated as -1. Since -1 is not equal to 2, the record is included in the results.

Method 3: Using IFNULL (MySQL Specific)

Similar to COALESCE, MySQL provides the IFNULL function to handle NULL values directly. It accepts two arguments: the expression to check and the fallback value to return if the expression is NULL.

SELECT name
FROM Customer
WHERE IFNULL(referee_id, 0) <> 2;

Here, any NULL entries are replaced by 0. This ensures that the comparison <> 2 evaluates correctly for both actual non-2 values and NULL values.

Method 4: Filtering with a Subquery

An alternative strategy involves identifying the set of customers who were referred by ID 2 and explicitly excluding them using a NOT IN clause.

SELECT name
FROM Customer
WHERE id NOT IN (
    SELECT id
    FROM Customer
    WHERE referee_id = 2
);