parkint — 2014-01-29T07:19:33-05:00 — #1
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.
r937 — 2014-01-29T07:45:06-05:00 — #2
that's pretty much it
except that it's the rows that get grafted together, not the tables
parkint — 2014-01-29T08:51:22-05:00 — #3
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.
r937 — 2014-01-29T12:33:04-05:00 — #4
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)
force — 2014-01-29T16:16:53-05:00 — #5
parkint — 2014-01-30T07:44:25-05:00 — #6
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'.
r937 — 2014-01-30T08:13:42-05:00 — #7
i never use right outer join... perhaps cultures where the written language is read from right to left do so more often