Find a value in one table that exists in multiple columns of another table-CodePudding

We have a table of CUSTOMER and a table of ACCOUNTS (there is no primary or foreign key in either table - long story!)

My data is such:

Accounts can 1, 2 or 3 owners. I need to find which CUSTOMERS aren't associated with any account.

What I have tried:

But the query is literally taking forever - even when I restrict the subquery to return the top 10 rows.

I want to see returned in my search Danny, Emma and Fang

CodePudding user response：

You forgot to relate the subquery to your main query. Your query says: "Give me all customers provided there is no row in the accounts table." It should say "Give me all customers for which there is no row in the accounts table."

select cutomername
from customer c
where not exists
(
  select null
  from accounts a
  where c.customername in (a.owner1, a.owner2, a.owner3)
);