Subject Re: [firebird-support] is there a way around the IN clause?
Author Martijn Tonies

> The orders table hold about 5 million records and the customers table
> 500000.
> The 'SAN' clause filters about 100000 order records and 5000 customers.
> I need to find a group of customers who have not placed and order in a
> specified period.
> The following query takes 44 seconds to return.
> select
>, c.surname, c.telephone, c.lastorderdate
> from customers c
> where not in (select customerlink from orders where recdate >
> '01-jan-2004' and recdate < '01-feb-2004' and branchcode = 'SAN')
> and c.branchcode = 'SAN'
> order by
> c.lastorderdate desc,1,2

How about a LEFT JOIN:
select, c.surname, c.telephone, c.lastorderdate, o.customerlink
from customers c left join orders o on (o.customerlink =
and o.recdate > '01-jan-2004' and o.recdate < '01-feb-2004' and o.branchcode
= 'SAN')
where c.branchcode = 'SAN' and o.customerlink is null
order by c.lastorderdate desc,1,2

I'm not sure if this returns the OK result, but give it a try :-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions