2

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?

Matt
  • 14,906
  • 27
  • 99
  • 149
user3260061
  • 65
  • 8
  • 1
    Please **[EDIT]** your question and add the `create table` statements for the tables in question (including all indexes), the query you are using and the execution plan generated using **`explain (analyze, verbose)`**. [**Formatted text**](http://stackoverflow.com/help/formatting) please (make sure you preserve the indention), [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) –  Sep 15 '17 at 12:46
  • 1
    The `NOT IN` criteria can be removed without changing the result. Pls review your question. – iDevlop Sep 15 '17 at 13:14
  • 1
    @PatrickHonorez - the way I read it, a given id could have multiple rows in the accounts table. If an id in that table has both a case where sid = 72 and sid = 80, then it shouldn't be counted, hence the need for the NOT IN – kjmerf Sep 15 '17 at 13:17
  • @kbball Got it. Interesting, and good you clarified it. – iDevlop Sep 15 '17 at 13:49

3 Answers3

3

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?

kjmerf
  • 4,275
  • 3
  • 21
  • 29
0

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)
Matt
  • 14,906
  • 27
  • 99
  • 149
  • The count would be too high, because you would potentially be counting ids that have a row in accounts where sid in (80, 81) – kjmerf Sep 15 '17 at 13:15
  • 2
    That won't give the right result if Customers have several Accounts – iDevlop Sep 15 '17 at 13:17
0

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)
)
John
  • 3,458
  • 4
  • 33
  • 54