<Not in> solution

  • Thread starter Thread starter Serguei Makacharipov
  • Start date Start date
S

Serguei Makacharipov

I just found new solution for some problem.
Understand that it is obviously that it is far away to be compared with
American continent discovery but anyway.
As I started to work with Access queries I found that constructions like
this
select * from t1
where t1.id not in
(Select extid from t2)
work very slow.
So i changed it to
SELECT Tbl.*
FROM
(SELECT t1.*, t2.extId FROM t1 LEFT JOIN t2 ON t1.id = t2.Ext.id) As tbl
WHERE tbl.extId is NULL
this kind of statement works hundred times faster than previous one.
Perhaps it will be some use for someone from my letter.
Serguei.
 
I missed <NOT> in second query in my post.
It has to be
WHERE tbl.extId is NOT NULL
instead of
WHERE tbl.extId is NULL
 
Serguei said:
I just found new solution for some problem.
Understand that it is obviously that it is far away to be compared with
American continent discovery but anyway.
As I started to work with Access queries I found that constructions like
this
select * from t1
where t1.id not in
(Select extid from t2)
work very slow.
So i changed it to
SELECT Tbl.*
FROM
(SELECT t1.*, t2.extId FROM t1 LEFT JOIN t2 ON t1.id = t2.Ext.id) As tbl
WHERE tbl.extId is NULL
this kind of statement works hundred times faster than previous one.
Click to expand...


To find all the records in t1 that have no matching entry in
t2, try using a "frustrated" outer join instead of a
subquery:

SELECT t1.*
FROM t1 LEFT JOIN t2
ON t1.id = t2.extid
WHERE t2.extid Is Null
 
Back
Top