Find Customer Referee is a very interesting problem of leetcode 584 and is also asked in many interviews with big to small companies. We will be going to solve this query.


Let's take the table first named customer:


CREATE TABLE If Not Exists Customer (id int, name varchar(25), referee_id int)


INSERT INTO Customer (id, name, referee_id) values ('1', 'Will', NULL)

INSERT INTO Customer (id, name, referee_id) values ('2', 'Jane', NULL)

INSERT INTO Customer (id, name, referee_id) values ('3', 'Alex', '2')

INSERT INTO Customer (id, name, referee_id) values ('4', 'Bill', NULL)

INSERT INTO Customer (id, name, referee_id) values ('5', 'Zack', '1')

INSERT INTO Customer (id, name, referee_id) values ('6', 'Mark', '2');


The customer table will look like this:


id name refree_id
1 Will NULL
2 Jane NULL
3 Alex 2
4 Bill NULL
5 Zack 1
6 Mark 2

This is the table and
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.

Question: Write an SQL query to report the names of the customer that are not referred by the customer with id = 2.

The query result will look like this:


name
Will
Jane
Bill
Zack


This is the question part. Looking very simple right?

Here is the fun part which starts.

You are trying to solve this question with a query:

SELECT name FROM customer WHERE refree_id!=2;

The output of the above query will look like this:

name
Zack

Now, you are thinking how is this possible, this blog is shit. Try it on your laptop and let me know.


Let's try another one:

SELECT name FROM customer WHERE refree_id!=2 OR refree_id=NULL;

The output of the above query:


name
Zack

Again same, but why?


MYSQL uses three value logic - TRUE, FALSE, and UNKNOWN. Anything compared to NULL is evaluated as UNKNOWN. That anything also includes 'NULL' itself. That is why MYSQL provides IS NULL and IS NOT NULL operators to check for NULL.

Now, the query for getting the correct output will include IS NULL.

SELECT name FROM customer WHERE refree_id IS NULL OR refree_id!=2;


This is the final query that will result in the required output.

I hope you understood the concept and if you have another way to solve this let us know in the comments section.