Billy Pang
tuxedo_man at hotmail.com
Thu May 5 21:22:11 CDT 2005
got it. thanks for everyone's response. Billy >From: Arthur Fuller <artful at rogers.com> >Reply-To: dba-sqlserver at databaseadvisors.com >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] outer joins? >Date: Thu, 05 May 2005 14:23:37 -0400 > >Well phrased, JWC. Billy's question makes no sense because it assumes an >either/or situation, which is not often the case. I will present one simple >example: > >tblEmployees > EmployeeID Identity( 0, 1 ) > etc/ >tblEmployeesChildren > EmployeeChildID Identity( 0, 1 ) > EmployeeID Integer -- foreign key into tblEmployees > >It may be interesting to find the employees with no children. OUTER JOIN is >perfect for this. > >SELECT * >FROM tblEmployees >OUTER JOIN tblEmployeesChildren >ON tblEmployees.EmployeeID = tblEmployeesChildren.EmployeeID >WHERE tblEmployeesChildren.EmployeeID IS NULL > >Admittedly an artificial example, but this kind of thing comes up more >often than you might initially imagine. Another case: assume a UDF that >returns a table containing Sales in the current year. List all customers >who have bought nothing this year. OUTER JOIN Customers to the UDF, testing >for NULL as above, and there's your list. >John W. Colby wrote: > >>Inner and outer joins simply perform different functions in the overall >>scheme of things so you can't really say "avoid them if possible". An >>inner >>join says "only give me records where there is data in both tables". This >>is used to enforce rules (for example) - if there is no invoice for this >>client, don't return a record in the invoice report - stuff like that. >>Outer joins are precisely to allow breaking such rules, allow me to see >>all >>clients, WITH ANY INVOICES... But also show clients where there is no >>invoice. >> >>Which you use depends entirely on your purpose with the query. You may be >>able to use an outer join to only display records where there is data in >>both tables, but that would require jumping through many hoops I'm >>guessing. >>And AFAIK you simply cannot use an inner join to display data where there >>is >>no data in the related table. >> >>They are just different animals used for different things. In my mind >>your >>question doesn't make sense. >> >>John W. Colby >>www.ColbyConsulting.com >> >>Contribute your unused CPU cycles to a good cause: >>http://folding.stanford.edu/ >> >>-----Original Message----- >>From: dba-sqlserver-bounces at databaseadvisors.com >>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Billy >>Pang >>Sent: Wednesday, May 04, 2005 2:48 AM >>To: dba-SQLServer at databaseadvisors.com >>Subject: [dba-SQLServer] outer joins? >> >> >>Hello: >> >>Can I ask for the group's opinion about something? Is there any >>performance >> >>differences between an INNER JOIN and an OUTER JOIN query? I know the two >>joins may or may not produce the same resultset (depending on data) but I >>am >> >>curious if anyone has come across any difference in terms of performance, >>generally speaking of course. Should OUTER JOINs be avoided in general >>unless absolutely necessary? >> >>Thanks >> >>Billy >> >> >>_______________________________________________ >>dba-SQLServer mailing list >>dba-SQLServer at databaseadvisors.com >>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>http://www.databaseadvisors.com >> >> >> >> >>_______________________________________________ >>dba-SQLServer mailing list >>dba-SQLServer at databaseadvisors.com >>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>http://www.databaseadvisors.com >> >> >> >> > > >-- >No virus found in this outgoing message. >Checked by AVG Anti-Virus. >Version: 7.0.308 / Virus Database: 266.11.5 - Release Date: 5/4/2005 > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com >