Natural Joins Creating More Records Than Desired

Question

I have the following tables populated with these records:

I have created a view that looks like this and have selected all records from it:

However, the results are not as expected. Each store location is matched with each craft item, even if they are not supplied to that store.

Even regions that don't have recorded stores display records:

I imagine this has something to do with the natural join being mixed with the left outer join, but I don't understand why.

Answer 1

And based on all the comments others have provided, and you may not be comfortable explicitly with the syntax, think of LEFT side as the first table of a query and right is the second. So a left join implies everything from the left-side table regardless of a match on the other, but if one exists, it only exists based on the matching criteria/condition. For what you have, you are probably looking for something like...

create or replace view detailedCraftRegaion as 
select
      cr.CraftRegionDescription,
      cs.StoreAddress,
      cs.StoreCity,
      cs.StoreState,
      cs.StoreZipCode,
      csi.CraftItemName

   from
      CraftStore cs
         JOIN CraftRegion cr
            on cs.CraftRegionID = cr.CraftRegionID
         JOIN CraftShipItems csi
            on cs.CraftStoreID = csi.CraftStoreID