I’ve been trying to improve my SQL and was playing around with a ‘NOT EXISTS’ function. I needed to find the names of salespeople who did not have any sales to company ‘RED’.
I tried this and it did not work:
SELECT DISTINCT
sp.name
FROM salesperson sp
WHERE NOT EXISTS (
SELECT
ord.sales_id
FROM
company cmp
LEFT JOIN orders ord
on cmp.com_id=ord.com_id
WHERE cmp.name = 'RED')
This query ran but returned a NULL. Then I changed it to this and it worked fine:
SELECT DISTINCT
sp.name
FROM salesperson sp
WHERE sp.sales_id NOT IN (
SELECT
ord.sales_id as sales_id
FROM
company cmp
left join orders ord
on cmp.com_id=ord.com_id
WHERE cmp.name = 'RED')
Can someone explain why ‘NOT EXISTS’ did not work in this instance?
.
.
.
.
.
.
Just in case, here is the exercise in full:
Given three tables: salesperson, company, orders
Output all the names in the table salesperson, who didn’t have sales to company ‘RED’.
Table: salesperson
| sales_id | name | salary | commission_rate | hire_date |
|---|---|---|---|---|
| 1 | John | 100000 | 6 | 4/1/2006 |
| 2 | Amy | 120000 | 5 | 5/1/2010 |
| 3 | Mark | 65000 | 12 | 12/25/2008 |
| 4 | Pam | 25000 | 25 | 1/1/2005 |
| 5 | Alex | 50000 | 10 | 2/3/2007 |
The table salesperson holds the salesperson information. Every salesperson has a sales_id and a name.
Table: company
| com_id | name | city |
|---|---|---|
| 1 | RED | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Boston |
| 4 | GREEN | Austin |
The table company holds the company information. Every company has a com_id and a name.
Table: orders
| order_id | order_date | com_id | sales_id | amount |
|---|---|---|---|---|
| 1 | 1/1/2014 | 3 | 4 | 100000 |
| 2 | 2/1/2014 | 4 | 5 | 5000 |
| 3 | 3/1/2014 | 1 | 1 | 50000 |
| 4 | 4/1/2014 | 1 | 4 | 25000 |
The table orders holds the sales record information, salesperson and customer company are represented by sales_id and com_id.
expected output
| name |
|---|
| Amy |
| Mark |
| Alex |
Explanation:
According to order ‘3’ and ‘4’ in table orders, it is easy to tell only salesperson ‘John’ and ‘Pam’ have sales to company ‘RED’, so we need to output all the other names in the table salesperson.
Advertisement
Answer
I think your two queries are totally different.
NOT EXISTS– this will return data when that subquery doesn’t return data. Which will always return some data so you will always get null. You need to join this subquery with the main query usingWHERE sp.sales_id = ord.sales_id AND cmp.name = 'RED'NOT IN– this is what you need for your purpose. You can see that it’s clearly giving you data for not in (subquery) condition.