[dba-SQLServer] outer joins?

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