SQL 50–584. Find Customer Referee

It’s an easy problem part of the SQL 50 interview plan that states:

---

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 the customer 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.

---

This is a good easy example of how it is important to read the schema and make the query on the right way.

At first glance you would just retrieve all names where the referee id is not two right? but through the schema you can see that the ids can also be null, so because of that you will also need to add that condition, with the result being:

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

Runtime: 914 ms, faster than 90.86% of MySQL online submissions for Find Customer Referee.
Memory Usage: 0B, less than 100.00% of MySQL online submissions for Find Customer Referee.

What’s the difference between <> and != ?

The first one is ISO Standard while the second one is not, so I tend to get with the standard, but feel free to use != as well.

Is there a way to have only one condition instead of two? yes there is, you can use coalesce or ifnull checks, but is not faster than two conditions:

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

Runtime: 1133 ms, faster than 50.93% of MySQL online submissions for Find Customer Referee.
Memory Usage: 0B, less than 100.00% of MySQL online submissions for Find Customer Referee.

---

That’s it! If there is anything thing else to discuss feel free to drop a comment, if I missed anything let me know so I can update accordingly.

Until next post! :)