SQL display ID of any customer who has never placed an order, using set-based operations only

45 Views Asked by At

Couldn't find anything on this, using set-based operations. Not sure if I'm on the right track, but I have 2 guesses on how to do this, but I'm not sure if I'm even close, or if MINUS is the right set-based operation to use:

SELECT customerid
FROM customer
MINUS
SELECT orderid
FROM custorder
WHERE custorder IS NULL;

or

SELECT customerid
FROM customer
MINUS
SELECT orderid
FROM custorder;

Suggestions on how to improve or what to change? Any help is appreciated. Could someone also possibly explain when to use UNION, UNION ALL, or INTERSECT on a similar sample like this?

Also, I'm not sure if I need these in 2 different tables or not. In this database, 2 of these tables are CUSTOMER (has customerid) and CUSTORDER (has customerid and orderid). Here's a screenshot of the table values if you wanted to see: https://i.stack.imgur.com/BFvbB.jpg

NOTE: I HAVE TO USE SET-BASED OPERATIONS ONLY FOR FULL CREDIT

2

There are 2 best solutions below

0
On

probably more like

select customerid 
from customer
MINUS
select customerid
from custorder;

you need to MINUS off the same type of values from the set

0
On

I would use not exists:

select c.*
from customer c
where not exists (select 1
                  from custorder co
                  where co.customerid = c.customerid
                 );

You can do something similar with not in and left join/where.

The set-based approach is:

SELECT customerid
FROM customer
MINUS
SELECT customerid
FROM custorder;

The advantage of the first method is that you can select additional columns from customer.