SQL query for EXCEPT or NOT IN-CodePudding

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:

(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:

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!

CodePudding user response：

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);