Have you tried this? select * from table1 LEFT JOIN table2 ON table1.id = table2.id where table2.id IS NULL
----- Original Message ----- From: "Scott Weikert" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Tuesday, February 11, 2003 3:23 PM Subject: Is there a better way to do this...? > I've got a query that, sometimes (based on user choice) I want to exclude > rows in based on a matching ID being in a second table, a la > > SELECT * > FROM Table1 > WHERE ID NOT IN (SELECT ID From Table2) > > or > > WHERE NOT EXISTS (SELECT ID From Table2 WHERE ID = Table1.ID) > > Either WHERE clause works fine. And these are very simplified examples. > > Depending on the data, selecting without checking for matches is super > fast, but checking against the matches can take an enormous amount of time > in comparison. For example, with one situation I'm testing on (8700+ > records, with just as many secondary matching records, except for two), > selecting w/no matching exclusion takes 50ms. Selecting with the exclusion > takes 73 seconds. > > There's *got* to be a better way to do this...? > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Get the mailserver that powers this list at http://www.coolfusion.com
