Thanks Ben, I didn't know that the Join would be less "peppy" then the Exists... I will add this to my bag of tricks. Thanks.
Travis D. Falls | Consultant RAFT.Net IT | 860.547.4070 | [EMAIL PROTECTED] -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of Ben Miller Sent: Wednesday, June 22, 2005 3:41 PM To: [email protected] Subject: Re: [AspNetAnyQuestionIsOk] Column Comparison You could use an outer join. If you used a LEFT JOIN on this then you could determine which one was NULL, but the one thing that the JOIN does is force a match and without indexes and stuff it could cause some performance decrease. But the EXISTS can do a SEEK with a temporary index or using one that exists, which could prove to be faster. But you are right, an OUTER join could be used as well. SELECT T1.EmailAddress FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.EmailAddress = T2.Email WHERE T2.Email IS NULL Ben Miller ----- Original Message ----- From: "Falls, Travis D (HTSC, CASD)" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Wednesday, June 22, 2005 12:43 PM Subject: RE: [AspNetAnyQuestionIsOk] Column Comparison >I thought this is where an OUTER JOIN comes in no? I am not SQL guru that > is for sure but if I remember reading the guru's guide to transact-sql I > thought that is what they used it for. I don't have the book at my desk > or > else I would have looked it up. > > Travis D. Falls | Consultant RAFT.Net IT | 860.547.4070 | > [EMAIL PROTECTED] > > > -----Original Message----- > From: [email protected] > [mailto:[EMAIL PROTECTED] Behalf Of Ben Miller > Sent: Wednesday, June 22, 2005 2:38 PM > To: [email protected] > Subject: Re: [AspNetAnyQuestionIsOk] Column Comparison > > > I would use something like this: > > SELECT DISTINCT T1.EmailAddress > FROM Table1 T1 > WHERE NOT EXISTS (SELECT 1 FROM Table2 WHERE Email = T1.EmailAddress) > > NOT EXISTS performs pretty quickly as it only checks that it would exist. > > Ben Miller > > > ----- Original Message ----- > From: "sas0riza" <[EMAIL PROTECTED]> > To: <[email protected]> > Sent: Wednesday, June 22, 2005 12:07 PM > Subject: [AspNetAnyQuestionIsOk] Column Comparison > > >> Hello, >> >> What is the best way to compare 2 columns in 2 different tables? >> >> For instance, both tables have an email column. Table1 has all the >> email addresses, while Table2 holds only the email addresses that >> have replied or completed a form. I want to compare the two and >> return a list of email addresses that haven't replied or completed a >> form yet. >> >> This is what I have, but I'm not sure if it is the proper way: >> >> SELECT Distinct [EmailAddress] >> FROM [db].[dbo].[Table1], [db].[dbo].[Table2] >> where [EmailAddress] not like [Email] >> GO >> >> >> Note: Table1 has the column EmailAddress and Table2 has the column >> Email. >> >> It seems to work, but for some reason, I would still get some email >> addresses returned even though a response was received from them. >> >> Any help is greatly appreciated. >> >> Thanks! >> >> >> >> >> >> >> Yahoo! Groups Links >> >> >> >> >> >> >> > > > > Yahoo! Groups Links > > > > > > > > ************************************************************************* > PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is > for the exclusive use of addressee and may contain proprietary, > confidential and/or privileged information. If you are not the intended > recipient, any use, copying, disclosure, dissemination or distribution is > strictly prohibited. If you are not the intended recipient, please notify > the sender immediately by return e-mail, delete this communication and > destroy all copies. > ************************************************************************* > > > > > Yahoo! Groups Links > > > > > > Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
