Skip to content
Advertisement

Why did the ‘NOT IN’ work but not the ‘NOT EXISTS’?

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.

  1. 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 using WHERE sp.sales_id = ord.sales_id AND cmp.name = 'RED'
  2. 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.
Advertisement