On Thu, October 5, 2006 16:43, Norman Palardy wrote: > > On Oct 05, 2006, at 9:15 AM, Toby Rush wrote: > >>>> SELECT tPersons.* FROM tPersons,tHats WHERE >>>> tHats.ownerIDNumber=tPersons.idNumber >>>> >>>> How can I do the opposite of that? In other words, how can I get a >>>> recordSet containing the rows that the above statement does not >>>> return? Thanks in advance... >>> >>> OK... you want all the data from tPersons where their id is NOT IN >>> the set of people that have a tHat >>> >>> the set of people who have tHats is >>> >>> select ownerIDNumber from tHats >>> >>> so those that have no tHat is >>> >>> select * from tPersons where idNumber not in ( select ownerIDNumber >>> from tHats ) >>> >> >> Norman, you rock! >> >> Aaron (who replied off-list) and Math, I see that your solutions >> also work, though it's going to take me a little time to wrap my >> brain around left outer join. I'm going to go with Norman's >> solution, since it seems a lot simpler. It appears that they all >> give me the same correct set of records... are there any benefits >> to using one or the other? > > Pro's and con's ? ... not really ... > > However the solution I gave will work on most any database engine. > However, if subqueries are not optimized it could be a tad slower. > The up side is you can use distinct and an index on the subquery to > get some extra speed. > > While SQL 92 and SQL 99 added a lot of syntax I've yet to find > anything that they added that I had not been doing in some other > fashion for years. > However, the join syntax is now at least standard so it's more > portable than all the various vendor mechanisms for achieving the > same thing. >
You may get a speed boost (potentially huge, depending on db engine) by using a correlated sub-query: select p.* from tPersons as p where p.idNumber not in ( select h.ownerIDNumber from tHats as h where h.ownerIDNumber = p.idNumber ) What's happening is the subquery is only looking for ownerIDNumber for selected person rather than every ownerIDNumber in tHats. If ownerIDNumber is indexed (as it should be) and there's the potential for a lot of hat records, then this syntax will really speed things up. Note: The aliases (p & h in this case) are often needed to make things clear and less ambiguos. Regards, -- Ian M. Jones ________________________________________ IMiJ Software http://www.imijsoft.com http://www.ianmjones.net (blog) _______________________________________________ Unsubscribe or switch delivery mode: <http://www.realsoftware.com/support/listmanager/> Search the archives of this list here: <http://support.realsoftware.com/listarchives/lists.html>
