Watch 10 video solutions for Sales Person, a easy level problem involving Database. This walkthrough by Everyday Data Science has 7,331 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: SalesPerson
+-----------------+---------+ | Column Name | Type | +-----------------+---------+ | sales_id | int | | name | varchar | | salary | int | | commission_rate | int | | hire_date | date | +-----------------+---------+ sales_id is the primary key (column with unique values) for this table. Each row of this table indicates the name and the ID of a salesperson alongside their salary, commission rate, and hire date.
Table: Company
+-------------+---------+ | Column Name | Type | +-------------+---------+ | com_id | int | | name | varchar | | city | varchar | +-------------+---------+ com_id is the primary key (column with unique values) for this table. Each row of this table indicates the name and the ID of a company and the city in which the company is located.
Table: Orders
+-------------+------+ | Column Name | Type | +-------------+------+ | order_id | int | | order_date | date | | com_id | int | | sales_id | int | | amount | int | +-------------+------+ order_id is the primary key (column with unique values) for this table. com_id is a foreign key (reference column) to com_id from the Company table. sales_id is a foreign key (reference column) to sales_id from the SalesPerson table. Each row of this table contains information about one order. This includes the ID of the company, the ID of the salesperson, the date of the order, and the amount paid.
Write a solution to find the names of all the salespersons who did not have any orders related to the company with the name "RED".
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: SalesPerson table: +----------+------+--------+-----------------+------------+ | sales_id | name | salary | commission_rate | hire_date | +----------+------+--------+-----------------+------------+ | 1 | John | 100000 | 6 | 4/1/2006 | | 2 | Amy | 12000 | 5 | 5/1/2010 | | 3 | Mark | 65000 | 12 | 12/25/2008 | | 4 | Pam | 25000 | 25 | 1/1/2005 | | 5 | Alex | 5000 | 10 | 2/3/2007 | +----------+------+--------+-----------------+------------+ 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 | 1/1/2014 | 3 | 4 | 10000 | | 2 | 2/1/2014 | 4 | 5 | 5000 | | 3 | 3/1/2014 | 1 | 1 | 50000 | | 4 | 4/1/2014 | 1 | 4 | 25000 | +----------+------------+--------+----------+--------+ Output: +------+ | name | +------+ | Amy | | Mark | | Alex | +------+ Explanation: According to orders 3 and 4 in the Orders table, it is easy to tell that only salesperson John and Pam have sales to company RED, so we report all the other names in the table salesperson.
Problem Overview: You are given three tables: salesperson, company, and orders. The task is to return the names of salespersons who never handled an order for the company named RED. The challenge is filtering salespeople based on the absence of a specific relationship across joined tables.
Approach 1: Using NOT EXISTS with Subquery (O(n) time, O(1) extra space)
This approach checks for the absence of a matching record using a correlated subquery. For each salesperson, a subquery searches the orders table joined with company to see if an order exists where the company name is RED. The NOT EXISTS predicate returns true only when no such record is found. The key insight is that databases can shortâcircuit existence checks efficiently, making this pattern common in SQL interview problems. Use this when you want a clear âexclude if any match existsâ condition.
Approach 2: Using LEFT JOIN and IS NULL (O(n) time, O(1) extra space)
This method performs a LEFT JOIN from salesperson to orders associated with the company RED. If a salesperson never handled a RED order, the joined columns from that filtered dataset remain NULL. Filtering rows with WHERE joined_column IS NULL removes anyone who actually matched RED. This pattern is widely used when solving absence queries using SQL joins. It is easy to read and often preferred when you already need joins for other conditions.
Approach 3: INNER JOIN with GROUP BY and HAVING (O(n) time, O(1) extra space)
This approach joins salesperson, orders, and company, then aggregates results by salesperson. A conditional aggregation counts how many orders are linked to the company RED. The HAVING clause keeps only those groups where the count of RED orders equals zero. This technique relies on grouping logic from database querying. It works well when the query already requires aggregation for other metrics.
Approach 4: LEFT JOIN with NULL Filtering After Company Match (O(n) time, O(1) extra space)
Another variation performs a join between orders and company first, restricting rows to RED. Then a LEFT JOIN connects that result to salesperson. Salespeople who never appear in the RED order set produce NULL values and are selected with a WHERE ... IS NULL filter. This separates the filtering step from the join logic and keeps the query modular.
Recommended for interviews: The NOT EXISTS solution is usually the cleanest and easiest to reason about. It directly expresses the requirement: return salespeople for whom no RED order exists. Showing the LEFT JOIN ... IS NULL alternative demonstrates deeper understanding of relational filtering patterns commonly used in SQL interview questions.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| NOT EXISTS with Subquery | O(n) | O(1) | Best when filtering rows that must not have a related record |
| LEFT JOIN + IS NULL | O(n) | O(1) | Common SQL pattern for detecting missing relationships |
| INNER JOIN with GROUP BY | O(n) | O(1) | Useful when aggregation or counts are already needed |
| LEFT JOIN with Filtered Dataset | O(n) | O(1) | Cleaner structure when isolating the RED company orders first |
