Joining database tables

How to display records from different database tables with SQL

In this article we'll introduce the concept of foreign keys in a database.
Let's look at an example in order to understand this concept.

In previous articles we played with SQL queries using something called "W3 Schools SQL Try It" tool, that we can find here.

This tool provides us with a database and several tables, like Products, Customers, Categories, and so on, so we can try out different SQL queries and learn how to select and display records.

Let's select all records from the table Products:

SELECT * FROM Products;

This query brings up a bunch of Product records with several attributes, organized in columns:

The first column, labeled ProductID, represents the primary key of this table. As we have seen before, the role of the primary key is to identify uniquely each record in a table. A primary key is typically a unique integer but it can be any value, as long as it's unique.

In the Products table, we also notice some descriptive columns like ProductName, Unit and Price, specific to each product.

A couple of other columns are different, though. SupplierId and CategoryId only contain an integer, and as we can guess, they somehow refer to records inside other tables: Suppliers and Categories, respectively.

ID fields in the Products table, that refer to other tables are called foreign keys, because they refer to a separate (or foreign) entity.

The role of foreign keys

But why do we even refer to other tables for this kind of information? After all, a supplier is specific to a product and its information could easily be stored inside the Products table, in a Supplier column.

Well, if each product had only one supplier, it would probably make sense to keep supplier data in one table. But typically, in the real world, several products may come from the same supplier, and we would have to enter the same supplier data in several records inside the Products table.

This means we would have the same, repeated information throughout our database table. This is not a good practice. If our supplier changed address, we would have to update the address information in several places in our table, and this would not be very efficient if there are thousands of records.

By creating a separate table for Suppliers (and product Categories), we can have a better separation of concerns. The Suppliers table can have all the data it needs and can change it at any time. The Products table, on the other hand, is only concerned with the SupplierID to find the supplier for its products, no matter what data the supplier keeps.

Finding and displaying connected data

We have just seen that keeping data in separate but related tables has its advantages. But, how do we access data in two different tables?

Let's say we want to display a list of products showing the supplier name next to each product. How can we find the right supplier for each product?

This is where the concept of a multi-table query comes into play. We can collect data from different tables with a SQL statement called JOIN. Here's how we use it.

We know, by looking at our database schema, that the Products table has a column called ProductName and the Suppliers table has a column called SupplierName.
We also know that Products has a SupplierID column, that acts as a foreign key to join Products and Suppliers.

Based on this knowledge, We can write a statement that joins these two tables by the SupplierID column, with this syntax:

SELECT ProductName, SupplierName 
FROM Products 
JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID;

Let's take a close look at what happens in the query above.

We first select the two columns we want displayed: ProductName and SupplierName. Since these columns belong to two different tables we must specify which tables they come from. They come from Products and Suppliers, so we join the two tables, with the JOIN statement.

But we can't stop here. We also need to specify the conditions of the join, that is, we must tell SQL which column to use to join the tables.

Since we know that both table have a SupplierId column that contains the same key, we specify this condition after the ON statement on the last line.

The expression Products.SupplierID means the SupplierID column of the Products table. In the same way, Suppliers.SupplierID means the SupplierID column of the Suppliers table.

The resulting SQL statement describes exactly which columns we want, which tables we need to connect (or join) and which columns in each table we want to use as the basis of the join.

These are the records pulled up by the query:

And with this, we have seen how to use JOIN to connect two tables based on a foreign key. We will see more options in future articles.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.