B2B: MySQL Joins and Foreign Keys

Welcome to the first of five articles in the Web Expose Back to Basics project. Each article is going to cover a seperate and distinct topic, with the aim of providing worthwhile beginner tutorials to aid in furthering your web development skills.

The topic for today is using the popular MySQL database to logically store and retrieve data stored in several tables, avoiding data duplication. This is commonly referred to as ‘joining tables’ - and the links between two or more tables are often called ‘foreign key links’. This article deals with MySQL because it is the most readily available and popular database for PHP developers at this point in time, but the examples should work with zero to minimum modifications on most other popular relational database systems.

Using a join to link two tables together refers to the fact that by identifying a common column in both tables, you can use this column to join the two tables together and return a combined result set.

In this example picture, the green columns illustrate two sets of data that are common between the two tables. For example, these could be two tables with user email addresses in them; you could then join the tables on that email column. In most cases though, you will hopefully have designed the database to avoid data duplication (called normalisation) and these common fields will usually be integer ID fields. We can now use a join statement to combine the two tables and return a result set that looks like this:

Here, all the columns from table 1 are laid out as normal, then the matching row’s columns from table 2 are appended to each row. This will only happen if you have the correct filter conditions in place though - without a proper WHERE statement, every row in table 1 will be duplicated as many times as there are rows in table 2, providing you with a potentially huge result set.

I realise the above might be confusing, so I think it’s time to do some proper examples with SQL. I’m using MySQL 4.1.6 and phpMyAdmin 2.8.04 to control it, but almost any version will do. phpMyAdmin isn’t required, but is a very good web-based interface to the MySQL RDBMS. If you’re accessing MySQL using the command line tool, remember that you never pass the password through on the command line arguments.

  mysql -u username -p -h localhost

MySQL will prompt you for the password; neglecting the -p argument will make MySQL attempt to connect with a blank password.

Using the command line tool, or phpMyAdmin’s query tool, create two test tables to play with:

Creating tables

  CREATE TABLE `b_products` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 100 ) NOT NULL ,
  `category_id` INT NOT NULL
  );

  CREATE TABLE `b_categories` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 100 ) NOT NULL
  );

Populate tables with some data

  INSERT INTO `b_categories`(`name`)
  VALUES ('DVD');

  INSERT INTO `b_products`(`name` , `category_id`)
  VALUES ('Flatline', '2');

(Whenever you see data being populated, you should create some of your own as I haven’t listed all the INSERTs here, to keep the text a bit shorter. If you scroll to the bottom of the post, you can download all the tables and data I used in SQL format.)

The 2 in the last line of the INSERT is the relevant category id you want to link the product to. As you can see, the product table has a category_id, which is called a foreign key, as it refers to a table foreign to the current table. This abstraction of data allows us to avoid having to repeat data (i.e. we don’t have to type ‘DVD’ next to each product, we can simply enter the ID of the ‘DVD’ category). If ‘DVD’ ever changes to ‘HD DVD’, all we have to do is update the category table entry, and everything else will update automatically.

Sample query - fetch all products and their matching categories

  SELECT *
  FROM `b_products` p, `b_categories` c
  WHERE p.category_id = c.id;

Placing an identifier after the table name in the FROM statement allows us to assign an alias to the table; we can then refer to fields inside that table specifically by using the alias.columnname syntax. This is an important step when using joins, often tables share columns with the same name, and you have to explicitly state which one you want to match or fetch.

Result

Here you can see that category_id and id are outlined in orange and next to each other - the first three columns are from the product table, and the last two columns from the category table - the join effect is apparent here.

On line 3 of the above query, you can see that we’re limiting each row to only return the correct row from the category table - i.e. we only want to fetch data where the product category ID is the same as the category ID in the external table.

Compare the above to the following query without a WHERE clause:

  SELECT *
  FROM `b_products` p, `b_categories` c;

If you run this query, you’ll see that each row of the products table is joined against every single row in the categories table, as described in the beginning of the post.

Fetch all CD products, and only select the 3 relevant columns

  SELECT p.id, p.name, c.name
  FROM `b_products` p, `b_categories` c
  WHERE p.category_id = c.id
  AND c.name LIKE 'CD';

Result

Advanced: count the number of products in each category

  SELECT COUNT(p.id) AS count, c.name
  FROM `b_products` p, `b_categories` c
  WHERE c.id = p.category_id
  GROUP BY c.name;

Result

Let’s move on to another example, one where the two tables can have multiple links between each other (sometimes called a has-and-belongs-to-many relationship).

Create two additional tables and some data links

  CREATE TABLE `b_owners_products` (
  `owner_id` INT NOT NULL ,
  `product_id` INT NOT NULL
  );

  CREATE TABLE `b_owners` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 100 ) NOT NULL
  );

  INSERT INTO `b_owners_products`(`owner_id` , `product_id`)
  VALUES ('4', '3');

We don’t have to modify the owners table, or the product table - all the link information is stored in a third external table called owners_products (the convention is to put the table names alphabetically, seperated by a ‘_’).

Linking the two tables, using the third as a reference

  SELECT *
  FROM `b_owners` o, `b_products` p, `b_owners_products` op
  WHERE o.id = op.owner_id
  AND p.id = op.product_id;

Result

Doing something useful with the link

  SELECT o.name, p.name
  FROM `b_owners` o, `b_products` p, `b_owners_products` op
  WHERE o.id = op.owner_id
  AND p.id = op.product_id
  AND o.name = 'Bob';

Result

This fetches all products belonging to ‘Bob’.

More information

1. Sample tables and data for this article
2. A detailed explanation of JOINs
3. Ensuring foreign key integrity
4. More information on JOINs