Sometimes not join in the from clause can have performance issues too plus that will only return customers that are in both tables. If you prefer ther other route youi can do this:
Select Distinct cust.CustId >From Customers cust LEFT JOIN orders ON cust.custID = orders.CustId Where Orders.CustId IS NULL Either way 98% of the time if you look at the excecution plan that is produced by SQL server you will find them to be the same. Other DBMS I don't know about. Adam H On Tue, 30 Nov 2004 11:05:11 -0600, Eric Creese <[EMAIL PROTECTED]> wrote: > Sometimes using NOT can have performance issues > > You can also do: > SELECT a.CustID > FROM Customers a,Orders b > WHERE a.CustID = b.CustID > > > > -----Original Message----- > From: Adam Haskell [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 30, 2004 11:06 AM > To: CF-Talk > Subject: Re: SQL Query question. Please help... > > Multiple ways heres one: > > SELECT CustID > FROM Customers > WHERE CustID NOT IN (Select CustID From Orders) > > Adam > > On Tue, 30 Nov 2004 12:01:30 -0500, Che Vilnonis <[EMAIL PROTECTED]> wrote: > > Say I have two simple queries: > > > > <cfquery name="getCustsFromCusts" datasource="#DSN#"> > > SELECT CustID > > FROM Customers > > </cfquery> > > > > [and that query returns 700 records.] > > > > <cfquery name="getCustsFromOrders" datasource="#DSN#"> > > SELECT CustID > > FROM Orders > > </cfquery> > > > > [and that query returns 500 records.] > > > > The first query gets all Customer IDs. The second query gets all Csutomer > > IDs that have placed orders. Is there some way in SQL, to combine these two > > queries into one and return only the Customer IDs that are in the > > 'Customers' table that ARE NOT in the 'Orders' table. Using this example, > > 200 records would be returned. > > > > Any help would be appreciated. > > Che > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:185689 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
