Rule #1 and Rule #2

Simple guide to deciphering query's rows relationship.

How can one know that B is one-to-one to A, or if B is many-to-one to A?

SELECT *
FROM A
JOIN B ON B.FieldNameHere = A.FieldNameHere

Two simple rules:

1. If the first table's primary key don't appear on second table's join condition, the second table is one-to-one to first table.
2. If the first table's primary key appear on second table's join condition, the second table is many-to-one to first table.

How one knows which field is the primary key?
They are easy to remember, there are only two(or three) variations of them, namely: Id, EntityId, Entity_Id; other variations are not encouraged.


Rule #1 If the first table's primary key don't appear on second table's join condition, the second table is one-to-one to first table. The second table will always return one row at most to first table. There's only one birth country to a person. Example:

SELECT * 
FROM Person
JOIN Country ON Country.CountryId = Person.BirthCountryId

Rule #2 If the first table's primary key appear on second table's join condition, the second table is many-to-one to first table. The second table will return zero or more rows to first table. Example:

SELECT * 
FROM Person
JOIN Bid ON Bid.PersonId = Person.PersonId

Would this technique still work on reverse query? Yeah it would, check this:

SELECT * 
FROM Bid
JOIN Person ON Person.PersonId = Bid.PersonId 

The first table's primary key don't appear on second table's join condition; so, second table is one-to-one to first table. One given bid always belong to one person only.


How about this?

SELECT * 
FROM Country
JOIN Person ON Person.BirthCountryId = Country.CountryId

The first table's primary key appears on second table's join condition, falls under rule #2, it means the second table is many-to-one to first table. There could be zero or more persons that was born on that country.

It would also.

Apparently there's some exceptions to this two rules, check this: http://www.ienablemuch.com/2010/12/exceptions-to-rule-1-and-rule-2-one-to.html