April 10th, 2013, 10:04 AM
Select records where no records in other select
I'm wondering if anybody could give me some advise what the best way is to write an sql-select(most performant)
We have a TABLE_A and a TABLE_B.
We need to find all the records from table_A which have a specific status that are not found back in TABLE_B.
select TABLE_A.number from TABLE_A where Status > 100 and status < 10000
where TABLE_A.number not in (select TABLE_B.number from TABLE_B where (TABLE_B.TYPE=1 or TABLE_B.TYPE =2) and (TABLE_B.ACTION is null or TABLE_B.ACTION > now());
In TABLE_B there are more than 20000 records. I notice that a select with "NOT IN" or "NOT EXISTS" is not so performant.
Can anybody advise how to write such a command on a performant way?
April 10th, 2013, 10:44 AM
ON table_b.number = table_a.number
AND table_b.type in ( 1,2 )
AND ( table_b.action IS NULL
OR table_b.action > NOW() )
WHERE table_a.status > 100
AND table_a.status < 10000
AND table_b.number IS NULL
April 10th, 2013, 11:02 AM
thnx for the suggestion, will give it a try
Originally Posted by r937
April 10th, 2013, 11:11 AM
When I try the explain , I notice the following:
ID select_type table type rows extra
1 SIMPLE table_A ALL 79671 Using where
1 SIMPLE table_B ALL 1011889 Using where
My select is:
ON table_b.nummer = table_a.dnummer
AND table_b.ttype in ( 1,801 )
AND ( table_b.tvolgendeactietijd IS NULL
OR table_b.tvolgendeactietijd > NOW() )
WHERE table_a.dstatus > 100
AND table_a.dstatus < 10000
AND table_b.nummer IS NULL
I presume I have to create some indices in order to let it go faster?