SQL query for EXCEPT or NOT IN

Question

I'm new to SQL and I am trying to write a query on PostgreSQL to find the cust_ID and customer name of each customer at the bank who only has a loan at the bank, and no account.

The database schema is:

The results should have only these 3 customers:

My two attempts for this query are:

1)

(SELECT DISTINCT borrower.cust_ID, customer_name
FROM borrower, customer
where borrower.cust_ID = customer.cust_ID)
except
(SELECT DISTINCT cust_ID, account_number
 FROM depositor)

Where my output is:

2)

SELECT DISTINCT borrower.cust_ID, customer_name
FROM borrower, customer
WHERE (borrower.cust_ID = customer.cust_ID) NOT IN (SELECT cust_ID 
                                                    FROM depositor)

output is: ERROR: operator does not exist: boolean = character varying LINE 3: where (borrower.cust_ID = customer.cust_ID) not in (select c... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 117

Please help me get this query right! Thank you!

please read https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question and edit your question — nbk, Jun 30 '22 at 20:46
Please don't use images. However, "NOT ON" is not valid SQL... — Andrew, Jun 30 '22 at 20:47
NOT IN will work for this. As you're learning, you'll see it's more flexible to do a left join and test if the join failed. Have fun with SQL! — Levin Magruder, Jun 30 '22 at 20:56
Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (**without errors**), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh, Jun 30 '22 at 20:57

score 1 · Accepted Answer · answered Jun 30 '22 at 21:02

You can use EXISTS and NOT EXISTS to apply the logic that you want:

SELECT c.cust_id, c.customer_name
FROM customer c
WHERE EXISTS (SELECT * FROM borrower b WHERE b.cust_id = c.cust_id)
  AND NOT EXISTS (SELECT * FROM depositor d WHERE d.cust_id = c.cust_id);

Or with IN and EXCEPT:

SELECT cust_id, customer_name
FROM customer 
WHERE cust_id IN (SELECT cust_id FROM borrower EXCEPT SELECT cust_id FROM depositor);

SQL query for EXCEPT or NOT IN

1 Answers1