CODEWITHSUNDEEP
sqloracle-database
Kilro
Kilro

Reputation: 39

Sql query optimisation for million records

I have 2 entities Customers and Agreements.

Every customer could have 0..* agreements which could be active=1 or closed=0.

I'm trying to select clients, who don't have any agreements (no records in table Agreements) or clients who have agreements with flag 'closed' = 0 in one query.

The agreements table has a few million records.

Any suggestions how to do it the best way?

Upvotes: 0

Views: 62

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I think you want exists and not exists, with this logic:

select c.*
from customers c
where not exists (select 1
                  from Agreements a
                  where a.customerid = c.customerid
                 ) or
     exists (select 1
             from Agreements a 
             where a.customerid = c.customerid and
                   a.closed = 0
            );

For performance, you want an index on agreements(customerid, closed).

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32031

use correlated subquery, i gused you a relational column customerid

select c* from customer c
where  exists( select 1 from Aggrements a where c.customerid=a.customerid
                                          and a.closed=0)
or (not exists ( select 1 from Aggrements a where c.customerid=a.customerid
                                          and a.active=1)
   )

if you dont have index on customerid column do it 1st otherwise query will take time

Upvotes: 1

Related Questions