# LeetCode 607: Sales Person

## Problem Restatement

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

The `SalesPerson` table stores salesperson information.

| Column | Type | Meaning |
|---|---|---|
| `sales_id` | int | Salesperson id |
| `name` | varchar | Salesperson name |
| `salary` | int | Salary |
| `commission_rate` | int | Commission rate |
| `hire_date` | date | Hire date |

The `Company` table stores company information.

| Column | Type | Meaning |
|---|---|---|
| `com_id` | int | Company id |
| `name` | varchar | Company name |
| `city` | varchar | Company city |

The `Orders` table stores order information.

| Column | Type | Meaning |
|---|---|---|
| `order_id` | int | Order id |
| `order_date` | date | Order date |
| `com_id` | int | Company id |
| `sales_id` | int | Salesperson id |
| `amount` | int | Order amount |

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:

```sql
SalesPerson
Company
Orders
```

Output column:

```sql
name
```

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

```sql
Company.name = 'RED'
```

and the order belongs to that salesperson.

## Example

`SalesPerson` table:

| sales_id | name | salary | commission_rate | hire_date |
|---:|---|---:|---:|---|
| 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 |

`Company` table:

| com_id | name | city |
|---:|---|---|
| 1 | RED | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Boston |
| 4 | GREEN | Austin |

`Orders` table:

| order_id | order_date | com_id | sales_id | amount |
|---:|---|---:|---:|---:|
| 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 |

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

Those orders belong to:

| sales_id | name |
|---:|---|
| 1 | John |
| 4 | Pam |

So we exclude John and Pam.

Output:

| name |
|---|
| Amy |
| Mark |
| Alex |

## 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:

```sql
Orders.com_id = Company.com_id
```

This tells us which company each order belongs to.

Then we filter:

```sql
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

```sql
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:

```sql
SELECT
    s.name
FROM SalesPerson AS s
```

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

```sql
WHERE NOT EXISTS (
    ...
)
```

Inside the subquery, we join orders to companies:

```sql
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:

```sql
WHERE o.sales_id = s.sales_id
```

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

The condition:

```sql
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:

| Symbol | Meaning |
|---|---|
| `s` | Number of rows in `SalesPerson` |
| `o` | Number of rows in `Orders` |
| `c` | Number of rows in `Company` |

| Metric | Value | Why |
|---|---:|---|
| Time | `O(s * (o + c))` worst case | For each salesperson, the subquery may search matching orders and companies |
| Space | `O(1)` to `O(o)` | Depends on the database execution plan |

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.

```sql
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:

```sql
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:

| name |
|---|
| Amy |
| Mark |
| Alex |

Additional case:

```sql
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.