CODEWITHSUNDEEP
sqlpostgresql
user3260061
user3260061

Reputation: 65

Why SQL "NOT IN" is so slow?

Below is my SQL code:

select count(1) 
from customers 
where id in(
    select custid
    from accounts
    where sid in(72,73,74,75,76,77,78,79)
) 
and id not in(
    select custid 
    from accounts 
    where sid in(80,81)
);

Tables are indexed properly. Can this code be rewritten for better performance?

Upvotes: 2

Views: 2813

Answers (3)

John
John

Reputation: 3996

A minus query might be more efficient. Something like this:

SELECT count(1) 
FROM 
(
SELECT c.id 
FROM customers c
INNER JOIN accounts a ON c.id = a.sid
WHERE id IN (72, 73, 74, 75, 76, 77, 78, 79)
MINUS
SELECT c.id 
FROM customers c
INNER JOIN accounts a ON c.id = a.sid
WHERE id IN (80,81)
)

Upvotes: 0

kjmerf
kjmerf

Reputation: 4345

You could also try EXISTS:

select count(1) 
from customers c
where exists (
    select 1
    from accounts a
    where sid in(72,73,74,75,76,77,78,79)
    and a.custid = c.custid
) 
and not exists (
    select 1
    from accounts a
    where sid in(80,81)
    and a.custid = c.custid
);

This might be helpful read: Difference between EXISTS and IN in SQL?

Upvotes: 3

Matt
Matt

Reputation: 15061

Join your tables rather than using 2 subquerys.

SELECT count(1) 
FROM customers c
INNER JOIN accounts a ON c.id = a.sid
WHERE id IN (72, 73, 74, 75, 76, 77, 78, 79)

Upvotes: 0

Related Questions