Unanswered: Table Relationships that aren't option 1 on Join Properties
I recently started a new job and I'm cleaning up some other guy's monstrosity of a mess. He seems to be a fan of creating relationships between tables, but when he does them, he doesn't use the default option 1 table joins (where join items in both tables are equal). Instead, he's using the other joins that I commonly use in left and right joins. What's the purpose of creating relationships like this? I like to use the Northwind database as my guide and I noticed that it doesn't have these kind of relationships at all, however, the Inventory Control sample database does. Are these relationships used for when data is being removed from tables and you want to ensure that you'll get a query result even if the other table doesn't have the data in it?
The purpose of doing this would be where one table represents the whole population and another table is being used to reference and add other information to the population.
As a simple example: A customer table might represent the entire population, you may have imported a third party table which cross references certain demographics about you customer e.g, income, other purchases e.t.c., not all of you customers may exist in the second table and so therefore it would only return information for a % of the population, as a statistician, manager e.t.c. you would want to know the info for the whole of your customer base, even the if its don't know