Customer table:

Orders table:

Seller table:

Q. Write an SQL query to report the names of all sellers who did not make any sales in 2020?

This question was asked to me in a data analyst interview and I have provided the below mentioned query to resolve this in sql. But my query is partially incorrect because it give those sellers too who make his sale in another year to along with 2020 like elizabeth because he made the sale in 2019 and 2020. SO want a query in sql which only tell the name of all seller who did make any sales in 2020 only.

select s.seller_name
from seller_table s
JOIN order_table o
ON s.seller_id = o.seller_id
where year(sale_date) <> 2020;

CodePudding user response：

switch to a not in or not exists.

  select seller_name from seller where id not in 
    (select seller_id from order_table where year(sale_date) = 2020)

CodePudding user response：

Your query selects all sellers that have an order in a year that is not 2020. This is not what you want.

You want to select sellers for which not exists an order in year 2020.

select s.seller_name
from seller_table s
where not exists
(
  select null
  from order_table o
  where o.seller_id = s.seller_id
  and year(o.sale_date) = 2020
);

There are other ways to achieve the same, but this is the most straight-forward.

CodePudding user response：

Two equivalent approaches:

select s.seller_name
from seller_table s
where not exists (select 1 from order_table where o.seller_id=s.seller_id and year(sale_date)=2020)

and

select s.seller_name
from seller_table s
left join order_table o on o.seller_id=s.seller_id and year(o.sale_date)=2020
where o.seller_id is null

The former expresses the intent of the query better; the latter better represents how the query will actually be executed.