LeetCode 607: Sales Person

Problem Restatement

We are given three tables: SalesPerson, Company, and Orders.

The SalesPerson table stores salesperson information.

The Company table stores company information.

The Orders table stores order information.

We need to return the names of all salespeople who did not have any order related to the company named "RED".

Return the result in any order. The official problem asks for salespersons with no order related to company RED.

Input and Output

Input tables:

SalesPerson
Company
Orders

Output column:

name

A salesperson should be included if there is no matching order where:

Company.name = 'RED'

and the order belongs to that salesperson.

Example

SalesPerson table:

Company table:

Orders table:

Orders related to RED are the orders where com_id = 1.

Those orders belong to:

So we exclude John and Pam.

Output:

First Thought: Find RED Salespeople, Then Exclude Them

The problem is easier if we split it into two parts.

First, find all salespeople who did have an order with RED.

Then return every salesperson whose sales_id is not in that set.

The key join is:

Orders.com_id = Company.com_id

This tells us which company each order belongs to.

Then we filter:

Company.name = 'RED'

Now the remaining Orders.sales_id values are the salespeople we must exclude.

Key Insight

This is an anti-join problem.

We want rows from SalesPerson for which no matching RED order exists.

The safest SQL pattern for this is NOT EXISTS.

NOT EXISTS checks, for each salesperson, whether there is any related order to company RED.

If no such order exists, we keep the salesperson.

Algorithm

For each row in SalesPerson:

1. Look for an order with the same sales_id.
2. Join that order to Company.
3. Keep only companies named "RED".
4. If no such row exists, return the salesperson’s name.

SQL Solution

SELECT
    s.name
FROM SalesPerson AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders AS o
    JOIN Company AS c
        ON o.com_id = c.com_id
    WHERE o.sales_id = s.sales_id
      AND c.name = 'RED'
);

Code Explanation

The outer query starts from all salespeople:

SELECT
    s.name
FROM SalesPerson AS s

Then the WHERE NOT EXISTS clause removes salespeople who have at least one order with RED.

WHERE NOT EXISTS (
    ...
)

Inside the subquery, we join orders to companies:

FROM Orders AS o
JOIN Company AS c
    ON o.com_id = c.com_id

This lets us check the company name for each order.

The condition:

WHERE o.sales_id = s.sales_id

connects the subquery to the current salesperson from the outer query.

The condition:

AND c.name = 'RED'

keeps only orders related to the company named RED.

So the subquery answers:

“Does this salesperson have at least one order with RED?”

If yes, NOT EXISTS is false, and the salesperson is excluded.

If no, NOT EXISTS is true, and the salesperson is returned.

Correctness

A salesperson is returned only if the NOT EXISTS subquery finds no order for that salesperson joined to a company whose name is RED.

Therefore, every returned salesperson has no order related to RED.

Conversely, suppose a salesperson has no order related to RED. Then the subquery cannot find a matching row for that salesperson. So NOT EXISTS evaluates to true, and the salesperson is returned.

Thus, the query returns exactly the salespeople who did not have any order related to company RED.

Complexity

Let:

With indexes on Orders.sales_id, Orders.com_id, and Company.com_id, the database can execute this much faster.

Alternative: NOT IN

Another common solution is to build the list of salespeople who sold to RED, then exclude them.

SELECT
    name
FROM SalesPerson
WHERE sales_id NOT IN (
    SELECT
        o.sales_id
    FROM Orders AS o
    JOIN Company AS c
        ON o.com_id = c.com_id
    WHERE c.name = 'RED'
);

This works for the LeetCode schema because sales_id values are not null in the relevant tables.

In general SQL, NOT EXISTS is often safer than NOT IN when null values might appear.

Testing

Sample data:

CREATE TABLE SalesPerson (
    sales_id INT,
    name VARCHAR(255),
    salary INT,
    commission_rate INT,
    hire_date DATE
);

CREATE TABLE Company (
    com_id INT,
    name VARCHAR(255),
    city VARCHAR(255)
);

CREATE TABLE Orders (
    order_id INT,
    order_date DATE,
    com_id INT,
    sales_id INT,
    amount INT
);

INSERT INTO SalesPerson VALUES
    (1, 'John', 100000, 6, '2006-04-01'),
    (2, 'Amy', 12000, 5, '2010-05-01'),
    (3, 'Mark', 65000, 12, '2008-12-25'),
    (4, 'Pam', 25000, 25, '2005-01-01'),
    (5, 'Alex', 5000, 10, '2007-02-03');

INSERT INTO Company VALUES
    (1, 'RED', 'Boston'),
    (2, 'ORANGE', 'New York'),
    (3, 'YELLOW', 'Boston'),
    (4, 'GREEN', 'Austin');

INSERT INTO Orders VALUES
    (1, '2014-01-01', 3, 4, 10000),
    (2, '2014-02-01', 4, 5, 5000),
    (3, '2014-03-01', 1, 1, 50000),
    (4, '2014-04-01', 1, 4, 25000);

Expected output:

Additional case:

TRUNCATE TABLE Orders;

INSERT INTO Orders VALUES
    (1, '2020-01-01', 2, 1, 100),
    (2, '2020-01-02', 3, 2, 200);

No salesperson has an order with RED, so all salespeople should be returned.