Today, we continue our journey into the world of SQL and relational database systems. In this part three of the series, we’ll learn how to work with multiple tables that have relationships with each other. First, we will go over some core concepts, and then will begin working with JOIN queries in SQL.
Catch Up
Introduction
When creating a database, common sense dictates that we use separate tables for different types of entities. Some examples are: customers, orders, items, messages etc… But we also need to have relationships between these tables. For instance, customers make orders, and orders contain items. These relationships need to be represented in the database. Also, when fetching data with SQL, we need to use certain types of JOIN queries to get what we need.
There are several types of database relationships. Today we are going to cover the following:
- One to One Relationships
- One to Many and Many to One Relationships
- Many to Many Relationships
- Self Referencing Relationships
When selecting data from multiple tables with relationships, we will be using the JOIN query. There are several types of JOIN’s, and we are going to learn about the the following:
- Cross Joins
- Natural Joins
- Inner Joins
- Left (Outer) Joins
- Right (Outer) Joins
We will also learn about the ON clause and the USING clause.
One to One Relationships
Let’s say you have a table for customers:
We can put the customer address information on a separate table:
Now we have a relationship between the Customers table and the Addresses table. If each address can belong to only one customer, this relationship is “One to One”. Keep in mind that this kind of relationship is not very common. Our initial table that included the address along with the customer could have worked fine in most cases.
Notice that now there is a field named “address_id” in the Customers table, that refers to the matching record in the Address table. This is called a “Foreign Key” and it is used for all kinds of database relationships. We will cover this subject later in the article.
We can visualize the relationship between the customer and address records like this:
Note that the existence of a relationship can be optional, like having a customer record that has no related address record.
One to Many and Many to One Relationships
This is the most commonly used type of relationship. Consider an e-commerce website, with the following:
- Customers can make many orders.
- Orders can contain many items.
- Items can have descriptions in many languages.
In these cases we would need to create “One to Many” relationships. Here is an example:
Each customer may have zero, one or multiple orders. But an order can belong to only one customer.
Many to Many Relationships
In some cases, you may need multiple instances on both sides of the relationship. For example, each order can contain multiple items. And each item can also be in multiple orders.
For these relationships, we need to create an extra table:
The Items_Orders table has only one purpose, and that is to create a “Many to Many” relationship between the items and the orders.
Here is a how we can visualize this kind of relationship:
If you want to include the items_orders records in the graph, it may look like this:
Self Referencing Relationships
This is used when a table needs to have a relationship with itself. For example, let’s say you have a referral program. Customers can refer other customers to your shopping website. The table may look like this:
Customers 102 and 103 were referred by the customer 101.
This actually can also be similar to “one to many” relationship since one customer can refer multiple customers. Also it can be visualized like a tree structure:
One customer might refer zero, one or multiple customers. Each customer can be referred by only one customer, or none at all.
If you would like to create a self referencing “many to many” relationship, you would need an extra table like just like we talked about in the last section.
Foreign Keys
So far we have only learned about some of the concepts. Now it is time to bring them to life using SQL. For this part, we need to understand what Foreign Keys are.
In the relationship examples above, we always had these “****_id” fields that referenced a column in another table. In this example, the customer_id column in the Orders table is a Foreign Key column:
With a database like MySQL, there are two ways to create foreign keys columns:
Defining the Foreign Key Explicitly
Let’s create a simple customers table:
CREATE TABLE customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(100) );
Now the orders table, which will contain a Foreign Key:
CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, amount DOUBLE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );
Both columns (customers.customer_id and orders.customer_id) should be the same exact data structure. If one is INT, the other one should not be BIGINT for example.
Please note that in MySQL only the InnoDB engine has full support for Foreign Keys. But other storage engines will still allow you to specify them without giving any errors. Also the Foreign Key column is indexed automatically, unless you specify another index for it.
Without Explicit Declaration
Same orders table can be created without explicitly declaring the customer_id column to be a Foreign Key:
CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, amount DOUBLE, INDEX (customer_id) );
When retrieving data with a JOIN query, you can still treat this column as a Foreign Key even though the database engine is not aware of that relationship.
SELECT * FROM orders JOIN customers USING(customer_id)
We are going to learn about JOIN queries further in the article.
Visualizing the Relationships
My current favorite software for designing databases and visualizing the Foreign Key relationships is MySQL Workbench.
Once you design your database, you can export the SQL and run it on your server. This comes in very handy for bigger and more complex database designs.
JOIN Queries
To retrieve data from a database that has relationships, we often need to use JOIN queries.
Before we get started, let’s create the tables and some sample data to work with.
CREATE TABLE customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(100) ); CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, amount DOUBLE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); INSERT INTO `customers` (`customer_id`, `customer_name`) VALUES (1, 'Adam'), (2, 'Andy'), (3, 'Joe'), (4, 'Sandy'); INSERT INTO `orders` (`order_id`, `customer_id`, `amount`) VALUES (1, 1, 19.99), (2, 1, 35.15), (3, 3, 17.56), (4, 4, 12.34);
We have 4 customers. One customer has two orders, two customers have one order each, and one customer has no order. Now let’s see the different kinds of JOIN queries we can run on these tables.
Cross Join
This is the default type of JOIN query when no condition is specified.
The result is a so called “Cartesian product” of the tables. It means that each row from the first table is matched with each row of the second table. Since each table had 4 rows, we ended up getting a result of 16 rows.
The JOIN keyword can be optionally replaced with a comma instead.
Of course this kind of result is usually not useful. So let’s look the other join types.
Natural Join
With this kind of JOIN query, the tables need to have a matching column name. In our case, both the tables have the customer_id column. So, MySQL will join the records only when the value of this column is matching on two records.
As you can see the customer_id column is only displayed once this time, because the database engine treats this as the common column. We can see the two orders placed by Adam, and the other two orders by Joe and Sandy. Finally we are getting some useful information.
Inner Join
When a join condition is specified, an Inner Join is performed. In this case, it would be a good idea to have the customer_id field match on both tables. The results should be similar to the Natural Join.
The results are the same except a small difference. The customer_id column is repeated twice, once for each table. The reason is, we merely asked the database to match the values on these two columns. But it is actually unaware that they represent the same information.
Let’s add some more conditions to the query.
This time we received only the orders over $15.
ON Clause
Before moving on to other join types, we need to look at the ON clause. This is useful for putting the JOIN conditions in a separate clause.
Now we can distinguish the JOIN condition from the WHERE clause conditions. But there is also a slight difference in functionality. We will see that in the LEFT JOIN examples.
USING Clause
USING clause is similar to the ON clause, but it’s shorter. If a column is the same name on both tables, we can specify it here.
In fact, this is much like the NATURAL JOIN, so the join column (customer_id) is not repeated twice in the results.
Left (Outer) Join
A LEFT JOIN is a type of Outer Join. In these queries, if there is no match found from the second table, the record from the first table is still displayed.
Even though Andy has no orders, his record is still being displayed. The values under the columns of the second table are set to NULL.
This is also useful for finding records that do not have relationships. For example, we can search for customers who have not placed any orders.
All we did was to look for NULL values for the order_id.
Also note that the OUTER keyword is optional. You can just use LEFT JOIN instead of LEFT OUTER JOIN.
Conditionals
Now let’s look at a query with a condition.
So what happened to Andy and Sandy? LEFT JOIN was supposed to return customers with no matching orders. The problem is that the WHERE clause is blocking those results. To get them we can try to include the NULL condition as well.
We got Andy but no Sandy. Still this does not look right. To get what we want, we need to use the ON clause.
Now we got everyone, and all orders above $15. As I said earlier, the ON clause sometimes has slightly different functionality than the WHERE clause. In an Outer Join like this one, rows are included even if they do not match the ON clause conditions.
Right (Outer) Join
A RIGHT OUTER JOIN works exactly the same, but the order of the tables are reversed.
This time we have no NULL results because every order has a matching customer record. We can change the order of the tables and get the same results as we did from the LEFT OUTER JOIN.
Now we have those NULL values because the customers table is on the right side of the join.
Conclusion
Thank you for reading the article. I hope you enjoyed it! Please leave your comments and questions, and have a great day!
Write a Plus Tutorial
Did you know that you can earn up to $600 for writing a PLUS tutorial and/or screencast for us? We’re looking for in depth and well-written tutorials on HTML, CSS, PHP, and JavaScript. If you’re of the ability, please contact Jeffrey at nettuts@tutsplus.com.
Please note that actual compensation will be dependent upon the quality of the final tutorial and screencast.
- Follow us on Twitter, or subscribe to the Nettuts+ RSS Feed for the best web development tutorials on the web.
Enjoyed this Post?
Subscribe to our RSS Feed, Follow us on Twitter or simply recommend us to friends and colleagues!
By Burak Guzel
Join our newsletter!
Create a Tutorial, Get Paid!
Have something to teach the world? Want to earn money doing it? Tutorials, screencasts, and articles published here on Nettuts+ are largely contributed by readers just like you! We'll pay you great money for good content. Find Out More
First! :D
Did you just first your own article? Is that even fair? LOL
LOL x)
(excuse-me x) )
is funnai
Ha ha, I LOL’d :)
Haha… :D
nice article. thank you.
Very useful, thanks.
Excellent roundup. Thanks!
Nice tut, Can’t wait to read through it fully.
This is great! Keep up the good work :)
Very good for starters. Maybe in next article you could write more about foreign keys.
Great tutorial!
You really have a talent to explain things very clearly – this is evident both from your SQL series here on nettuts and the CI/Doctrine series on your personal blog. Keep up the good work, you are helping many people.
PS. That being said, could you please continue with part 10 of your CI series? :-) That would be much appreciated.
Thank you!
Yes, I will continue writing those. Sorry for the delay.
good article. keep up with the good work :)
That’s good !
Thanks !
Useful !
Great article! This is one that definitely will be referenced. I’m not too great with joins yet… as my co-workers can attest to.
Would be nice to see this also for Microsoft SQL server 2005/2008
good article (:
I hope ‘SQL for Beginners: Part 4 “will explain about the replication thx ^ _ ^
Great Article!
Well done. And thanks for the introduction to this great tool (MySQL workbench), it comes pretty handy when handling more complex databases and table relationships.
Thanks again
Great refresher i forgot about the on clause , never really use it :s
Anyway thanks :)
great tutorial
Great tutorial collection :-)
nice tut
thank you
Nice article on database normalization.
Really nice and clearly written.
waiting for a some advance topics also, like views, triggers, stored procedures.
Very good tutorial. Wish I had this 3 years ago when I was learning MySQL. Keep up the good work! MySQL workbench is an awesome piece of software as well. It’s very handy to be able to visually look at how all your data connects.
Check out MySQL Query Browser. It is a nice desktop replacement for web apps like PHPMyAdmin – and It’s made by the same people.
What about 3-4-5 joins in one query, ambiguous collumn names error, aliases for collumn names, get collumn as etc? ?
When You make 3+ collumn JOINS You can write something about how the order of those joins affect the query :)
There is lot more You can do, but You have to use more then two tables :)
But Your writing style, basics … this is realy nice stuff and lot of ppl will learn how to do things right :)
Yeah, there is a lot to talk about. I’m trying to just cover the basics for now, without scaring away the beginners.
old memories studying oracle DB in college, but most of the time all the students sleep in that class, me too :P
nice, thanks :D
Cool! Thanks!
wow,,,
great tutorial,,it is detailed….
but, i don’t have time to read it,,,
there are also some options if you want to add a foreign key constraint
like…
ALTER TABLE borrowed
ADD CONSTRAINT FK_borrowed
FOREIGN KEY (employeeid) REFERENCES employee(id)
ON UPDATE CASCADE
ON DELETE CASCADE;
great tutorial…
Great job, thanks!
Nice round up.
Will definitely give workbench a try.
Font used for the graph please ??
I really had dreams about this font…
Myriad Pro
Hi,
Thanks for this great tutorial this has by far been the easiest to understand one that i have ever been reading.
I have a question.
I have a member system that currently is only using one table to store all users data, for efficency and performance i want to split the profile information up onto a seperate table to keep things in order and clean and for performance reasons.
currently i have a table called “users” and there are 19 columns, this is not good if i am rite. The columns are
id, status, admin, failed_login, last_visited, username, first_name, last_name, email, password, registration_date_time, ip, activationkey, gender, website, msn, aim, yim.
What i want to do is place registration_date_time, ip, gender, website, msn, aim and yim on there own table. Now i am not sure what is the best join, for this, this is what confuses me. Currently the user id is determined by MySQL as each time a new user is created the id field is auto incremented by MySQL, problem is how can i get it to submit the id from the users table to the profile table? as until the information is sent to the users table there is no way to detrmine the id value and therefore there is no way to link and make sure both tables use the same id so records dont get mixed up.
Could someone please clarify what is the best join and how to make sure the profile table “id” is matching the id of the user in the “users” table, if it is autoincrememnted by mysql which in my case it it i dont know how it works to the id in users table is matching the id in the profiles table, i would love to do this before my website gets very large so i am using mysql to it’s full advantage.
Thanks
After you insert a record, call mysql_insert_id() to get the id. http://php.net/manual/en/function.mysql-insert-id.php
Hi,
thanks for replying, never new that function existed, I was going to user the username to join but guess i dont have to now, was not kean on using the username for join for many reasons.
Can i ask what join would be best? . They are somewhat confusing and not sure what join i would be best using.
Once again thanks for the reply :)
There is no “best” join as they all have different purposes. In your case, a LEFT JOIN (users LEFT JOIN users_details) might work.
Thanks a lot. Good tutorial:)
Great Tutorial! I would like to read more about Views, Procedures etc! I dont understand what they are for :P
Maybe there already is a good guide out there? Anyone?
Thanks-a-loto
I feel a lot smarter now! Thanks!
Great article! I’ve been trying to troubleshoot a problem, that up to now I just threw a band-aid on.
I need to join all the data across my database and some of the tables have the many to many relationships.
Here’s a basic table structure: Objects – Objects Taxonomy – Objects Relationships – Term Relationships – Term Taxonomy – Terms
The issue specifically arises because of the relationship between objects and terms. There are multiple results for each object, when all I need is to display each object once.
Currently, I just wrote a script to ignore duplicate results when it displays the results. (my band-aid fix) However, the query takes longer to process because of the number of results. Currently I’m using Left Joins.
Don’t know if there is a way to do this with SQL. Queries are definitely not my strength. Thanks for any advice you can offer!
Thanks so much for the great tutorials!! Very useful information, and you did a great job of breaking it down into explanations!
Hope there is a Part 4, 5 & 6 in the works? :D
Thanks again dude
Yeah I wish they continue these SQL Tutorials ,
I cant find any tutorials out there that gives good explanation like this
Thank you so much, finally i got the idea of JOIN and relations
Thanks for this tutorial. I was looking to learn different relationship between different tables in the database. This article helped me to understand this quite clearly.
cool tutorial
sorry for double comment
but I need an tutorial about creating a search form which will query the mysql database
can anyone suggest anything?
I ve seen one on the other website but it cost £40
nice article
The part on various JOINs is very useful.
Thank you very much.
loved it! very concise
thanks
Good morning!
When I enter the following SQL expresion:
SELECT * FROM customers JOIN orders;
I obtain the following result:
http://lh5.ggpht.com/_UnHcjnFtcdE/TNbDAw4TKzI/AAAAAAAAB2k/JGH0q2MH6mA/sqlNaturalJoinOperation.png
What’s is happening? The result is not equals to http://d2o0t5hpnwv4c1.cloudfront.net/538_sql3/ss_9.png
Thanks in advance!
Looks like you named the column customers_customer_id , instead of customer_id.
Hey, i found this article so easy to learn..
The join was so interesting like never before.
Thanks alot !
This article is useful for those who are learning SQL. and the comments are helpful.
These tutorials are absolutely amazing! Everything is so clear and well explained; thank you so much!
Cool tutorial! Thanks for this. Got some ideas now on how to easily teach my non-IT students database concepts.
gr8 work..It rly helps me..thnx a lot.. :)