How to know when to use Outer Join 1

[WhiteKnight2K]

Hello. I know this probably sounds like a dumb question, but I'm having trouble determining when to use a Left OUter Join vs Inner Join. When i wasnt getting the results I wanted from the default Inner Join, I switched one of the links to Outer Join and voila, the result i wanted appeared...i'm still not completely sure that I understand why. Maybe it would help to provide an example of the situation i had?

 

[DallasAggie]

Here's a good definition:

http://www.techonthenet.com/sql/joins.htm

 

[WhiteKnight2K]

ok thanks...thats a fairly basic article and I understand what a Left Outer Join is, I guess what I'm having a problem with is how these relate to the Link Tables screen and why the inner join does not give me the result I want.

 

[Madawc]

What result are you expecting? Give an example of what you expect, and then what you get.

The 'left outer' is the normal method when you link table A to table B, but some records in table A will have no equivalent in table B, and you still want them. Accounts with a postal address, say, and not all of them have a postal address.

One classic problem in Crystal is when you add an extra test to table B - excluding ceased records, say. Unless you allow for the tested value being null, table A records without a table B entry will now be excluded.

Madawc Williams (East Anglia, UK)

 

[rosemaryl]

Dear WhiteKnight2k,

The way I explain and show this to my students is that if you are linking from Table A to Table B and you want all of Table A and any matches in the table, then you want a Left Outer Join.

Do this exercise whenever you have to join tables:

Draw 2 circles with an intersection (a simple Venn Diagram). Color the left Circle Blue and the Right Circle Yellow, the intersection will naturally turn green because Blue and Yellow make green.

Label the one on the Left Table A and the one on the Right Table B. Use your actual table names to help you understand the relationship.

An inner join (=) join. Would return the records where the two circles intersect. In other words, the value you are joining on matches in both tables. (The Green Part)

A Left Outer join would return ALL records in the left table and any records (the intersection) where there is a match. (Blue and Green parts)

A Right Outer Join would return ALL the records in the Right table and any records (the intersection) where there is a match on the joined field. Green and Yellow parts.

A Full Outer Join (introduced in CR 10 I believe) will return all combinations of the records and can result in interesting data.

Think through this exercise.

Payroll has decided to implement a system where all paychecks must contain the deparment Name on them. There is a Department ID in the Employee table, but not the name of the deparment, so you decide that you must link the Employee and Department tables.

Now, you are designing the check print report and you perform an Equal Join from Dept.DeptID to Employee.DeptID, the check print is run on Thursday and checks are delivered to the employees on Friday morning. By Friday afternoon disgruntled employees without paychecks are lining up outside your door.

What happened? Well, since you performed an Equal join on the two tables, anyone who did not have a Dept ID populated in their Employee table was not pulled and hence no check for them.

Aha, you say, I will do a Left Outer Join from Dept to Employee solving this issue. The checks are run and again, you have disgruntled employees demanding their paychecks and in addition you have a ticked off payroll department with wasted checks that just have a Dept ID and no employee data on them.

Ooops, what happened? Well, a LEFT outer join from Dept To Employee returns all Departments and any that match. If the employee didn't have a dept id they still didn't get pulled. However, any Departments without employees did get pulled.

In the above scenario two joins would make sure that you got the employees at least, off your back:

A Right Outer join to Employee would return all employees and populate the dept name if the dept id in Employee was populated (you could also flip it around and do a Left Outer join from Employee to Department) and a Full Outer Join.

My recommendation is the Right outer join given a link from Department to Employee.

The absolute correct answer, of course, is to correct the data and make sure that every employee is populated.

I hope that helped understand the joins, I think you will find the Venn Diagram helps you when you are first working with joins.

Regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762