Join us!


Following to my latest articles on the basics of databases and tables I will now explain the principle of joining tables.

This is actually very, very easy. It may seem complex at first but it really is nothing.

Suppose we have our person table as we did before with the first name, lastname, gender and birthdate. Now let’s add a second table to our database which contains information about websites.
Let’s say we have the URL, Type, creation date and a unique ID in this table.

Now what you want to do is to store information about which person has created which website… This forms a small problem. Because you have info about the person and you have info about the website. You only do not know how to link the website to a person.
The solution is however easy. In the person table a new column should be added which contains the unique website ID. This ID is the same ID as in the website table!
So in stead of creating a third table or to store the info of the website also on the person table or the other way around. You just store the website ID in the person table.

So, small recap:
We have following information
Now to store information about who created which website, we add an extra column in the person table:
What we can do now is ‘join’ both tables together.
This mean that we can send a request to the database (which is called an SQL) with the question to return us information about the person and which website this person is linked to.
In this SQL you will define that the WebID column of the person table, should be the same as the ID of the website table.

So suppose we write an query (SQL) which says give me PersonID + WebID + First Name, WebsiteID + URL.. The result will be as such:

Notice that you will ONLY get 2 rows. Although you have 3 people in the person table.
This is because the third person does NOT have a website. Which means that the link or the JOIN does not exist between these 2 tables.
Also notice that the WebID and the ID (second one) are the same. These are the columns which we used to join.

So, joining is actually bringing 2 or more tables together in 1 resultset. A lot more is there to be said about joins. But make sure that you understand previous concept first. If not, read it again or send me a message/comment. I’ll be more than happy to explain again :).

Before continuing I need to tell you what an ERD is. No it ain’t some kind of drug… ;-)
And ERD is an Entity Relationship Diagram. Nothing more then a schema which defines all the tables (or a least a part of the tables) in a database and how they are linked.
That’s all, nothing more :-).. Believe it or not.

Let me show you a small example:
The line in between is the JOIN. However did not define which fields you need to use to construct this join.
A few things need to be said. You see that the join has 3 small lines at the end. Touching the website table.
This defined the cardinality of the join. This means that you can see how the data is organized by this line or what the relationship is between these tables.
In this case the join has the cardinality One to Many. Meaning that 1 person can have Multiple websites.
There a different types of cardinality.
This one is as said, One to more… Following also exist:
One to One
Many to Many

You get the point right. Bottom line, it defines how your data is organized (huraaay!).

Why is this important? Well, by using this schema you will now know how to define your query (SQL) to you will get the results you expect.
The example I have shown you above, for example, will only show you a person if this person has a website.

This type of join is called an INNER JOIN. So, data is only shown when the corresponding key is found in both tables.
You also have a few other types of joins (and this is why the cardinality is important to know)

LEFT OUTER JOIN: This will always show ALL data of the table which is defined on the LEFT side of your query (SQL)
RIGHT OUTER JOIN: do I really need to explain?
FULL OUTER JOIN: Always shows ALL data from ALL tables in the query
SELF JOIN: for some reasons you may want to join a specific table to itself. You can do this by entering the same table twice (or even more) in the same query.

Let me show you an example of a left outer join in our example model.. If we do the same query on the database but with instead of an inner join a left outer join. The results will look like this:

Note that only data from the person table is show in the last record. Simply because there is not data available for the website.

Complex? Not at all :D

You can manage, I just know you can!

And since we came this far, we might as well go all the way. Next time, I’ll show you some actual SQL.

 

Advertisements

One thought on “Join us!

  1. Pingback: Stars and Relations « Tech-o-rama

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: