Good grasp on the concept of Database JOIN

Databases
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.

ATdhvaannkcse

2

that’s pretty much it

except that it’s the rows that get grafted together, not the tables

:slight_smile:

3

Thanks, @r937;
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:


//users
id, fname, lname, country, fav_color

//transactions
id, order_number, user_id, item_id

//items 
id, color

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.

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)

yes

:slight_smile:

5

Another resource that might help:

6

Thank you both (@r937;, @Force_Flow:wink: 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’.

7

well spotted

i never use right outer join… perhaps cultures where the written language is read from right to left do so more often