Mar 15, 2005 #1 leo6 Programmer Joined Mar 2, 2005 Messages 22 Location US 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 ?
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 ?
Mar 15, 2005 #2 dbomrrsm Programmer Joined Feb 20, 2004 Messages 1,709 Location GB Code: 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] Upvote 0 Downvote
Code: 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]
Mar 16, 2005 #3 r937 Technical User Joined Jun 30, 2002 Messages 8,847 Location CA you do realize you wrote B.COL1=B.COL1 initially, right? here are two other solutions -- Code: select distinct COL1 from TAB1 as A where COL2 = constant and not exists ( select * from TAB2 where COL1 = A.COL1 ) Code: 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) Upvote 0 Downvote
you do realize you wrote B.COL1=B.COL1 initially, right? here are two other solutions -- Code: select distinct COL1 from TAB1 as A where COL2 = constant and not exists ( select * from TAB2 where COL1 = A.COL1 ) Code: 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)