query subselect

[leo6]

I have a query as follows ,

SELECT DISTINCT A.COL1
FROM TAB1 A
WHERE A.COL2=constant
AND A.COL1 NOT IN
(SELECT B.COL1
FROM TAB2 B
WHERE B.COL1=B.COL1 )

can this be written in any other way to run better ?

 

[dbomrrsm]

SELECT DISTINCT COL1
FROM TAB1
WHERE COL2=constant
AND COL1 NOT IN 
(SELECT COL1 
 FROM TAB2)

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[r937]

you do realize you wrote B.COL1=B.COL1 initially, right?

here are two other solutions --

select distinct COL1
  from TAB1 as A
 where COL2 = constant
   and not exists
       ( select *
           from TAB2 
          where COL1 = A.COL1 )

select distinct A.COL1
  from TAB1 as A
left outer
  join TAB2 as B
    on A.COL1 = B.COL1 
 where A.COL2 = constant
   and B.COL1 is null

you might also consider removing DISTINCT if in fact the values of A.COL1 are already unique

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)