After 10 Years Still Weird for Me and Confusing. - Outer Joins in Microsoft Access


	Back to Outer Joins Comments List

After 10 Years Still Weird for Me and Confusing.

Maurice Loucel

3 hours ago

YES.

I'm sick but as long as I have breath I will keep learning.

The Outer Join has been a struggle for me, not because I do not understand but because the way it is "Described":
1) "Include ALL records from CustomerT"
2) "AND ONLY THOSE RECORDS FROM ContactT"
3) Where the JOINED fields are equal.

The DESCRIPTION seems not logical Mr. Spock.
If I have 2 records Joined by CustomerID, and 1 has a record in both tables but the other one has not, then LOGICALLY Mr. Spock ONLY 1 will be displayed (see #3), because the statement says in #2 ONLY THOSE RECORDS and 3 says "THAT ARE EQUAL", since record #2 does not even exist in ContactT, logically should not even show up.

I believe the correct verbiage should say:

1) "Include ALL records from CustomerT"
2) "AND ALL THOSE RECORDS FROM ContactT"
3) Where the JOINED fields have data or not or exist in ContactT.

All records will be displayed from CustomerT regardless if they have data in ContactT.

This the way I understood after a while.

Richard Rost @Reply

3 hours ago

Sorry you're not feeling well, Maurice. Keep fighting, and keep learning.

You're right that the wording can be confusing. The key is that "only those records from ContactT where the joined fields are equal" applies to which ContactT records get attached to each CustomerT record. It does not eliminate any CustomerT records.

A clearer way to say it would be:

"Show every record from CustomerT, and show matching records from ContactT when they exist."

Your version saying "all records from ContactT" would imply that unmatched ContactT records should appear too, which would be a full outer join - a different animal entirely.

Next Unseen