I have worked with databases (or would that be databii?) of many flavors and for many, many years. And SQL is a language I am not afraid to approach. But by no means do I profess to be well versed in it.
I have a continuous struggle fully grasping the concept (and detailed implications) of the database join. My conceptual impression is that a JOIN represents a 'grafting' of two tables, linking them by a common field.
Is that close to correct?
Can someone offer a clear, descriptive explanation and some good analogy to help crystallize the concept?
I am calling on some of our Database experts, like @r937; to help me here.
that's pretty much it
except that it's the rows that get grafted together, not the tables
But can you offer an example? A sample?
I don't mean to appear dense but this is one of those areas where every time I attempt to apply what I think I know, it confounds me.
I understand the idea of normalizing a relational database. So, assuming I have these fictitious tables:
id, fname, lname, country, fav_color
id, order_number, user_id, item_id
If I wanted to get the number of orders each user placed that included an item of their favorite color. Is this a good example of where to use a JOIN?
I want to keep this discussion simple. At the same time, PLEASE correct my illustration where you can better facilitate edifying me.
the illustrations from chapter 3 of my sitepoint book, if you'll excuse me for saying, are optimally simple yet comprehensive... look for the join diagrams part way through Simply SQL: The FROM Clause
caution: code samples in that online article are b0rked, they are ~not~ the same as in the original printed book (the "leading commas" part is especially dumbfounding)
Thank you both (@r937;, @Force_Flow for your guidance. That article was very helpful. The simple Venn Diagrams are a great boon.
The subtlety of LEFT versus RIGHT versus OUTER is still a bit foggy to me. It appears a choice between LEFT and RIGHT is quite arbitrary because it is relative to the order in which you list the tables.
I need to find opportunities to practice and explore this. I suspect that is part of my problem; the need does not arise often enough for it to become 'second nature'.
i never use right outer join... perhaps cultures where the written language is read from right to left do so more often
This topic is now closed. New replies are no longer allowed.