Sign in or start a free trial to avail of this feature.
9. Combining Database Tables
Database tables can be combined through appending or joining them together. In this lesson, we’ll introduce both of these concepts and explain the various different types of join you might use.
The goal of this lesson is to learn how to combine database tables.
When we’re combining tables with the same set of fields, we use a Union Join, which is also called appending tables. Appending adds the rows from the second table to the bottom of the first table. The structure of the table does not change.
Things are more complicated when the tables to be combined contain different sets of columns. In this case, we use the method of joining tables. To do this, we need to find a column which is common to both tables. This column will be the primary key of one of the tables to be joined. In the other table, it is known as the foreign key.
We use foreign keys and primary keys to join tables together. The keys are used to match the rows from the two tables to each other. The result is a table containing all the values of the primary key and foreign key, as well as all the columns contained in both tables.
Joining is relatively simple when the primary key and foreign key contain the same set of values. In some situations, this won’t be the case, and we’ll need to decide which records should appear in the combined table. There are several different join types that you can use:
- Full Outer Join: This includes all the records from both tables.
- Inner Join: This includes only records where the keys appear in both tables.
- Left Join: This includes only records where the key appears in one of the two tables.
- Left Join Without Intersection: This is similar to the left join but removes records where the keys appear in both tables.
As mentioned in lesson six, most databases you'll come across are relational databases.
In this kind of database, some of the fields are common across two or more tables.
We can use these common fields to combine the tables together.
In this lesson, we'll learn how to combine database tables. There are several ways of combining tables, mostly through joins.
Joins can be helpful if you want to incorporate data from one table into another related table. There are several different types of join, which can be useful in different situations.
Let's start by looking at a union, or a union join, sometimes also called appending data.
Here we see two tables showing sales data for 2017 and 2018.
The fields included in both tables are identical. However, the data differs between the two years. Most of the time, it's preferable to store sales data in a single table for both years.
We can do this by combining these two tables together in a union.
This simply adds the rows from the second table to the bottom of the first table, as we can see here.
Because both tables have the same column layout, there's no need to modify the table structure when we combine them.
However, when you join tables, the column layouts often are not identical. Let's consider another example.
We'll now add a table of product information to our database.
As we can see, the Product ID field appears in both tables, but otherwise the two tables contain a different set of columns.
As a result, the appending method we saw before will not work. Instead of combining the rows of both tables, we must combine the columns.
Before we start joining, let's consider how the keys work in these tables. In the Product Info table, the Product ID field is the primary key, as each product has a unique ID. The Sales table is different.
Let's assume each product can have multiple sales but only one sale per day.
This means that we can uniquely identify a sales record by combining its Product ID and Date fields. This is called a compound key.
Finally, let's consider the Product ID column in the Sales table.
This provides a link to the primary key of another table.
A column like this is called a foreign key.
In a real database, a table could have multiple foreign keys.
We use foreign keys and primary keys to join database tables together.
Let's see how we can join these two tables.
For each row in the Sales table, we identify the appropriate product from the Product ID column, and add the appropriate values from the Product Info table.
Here we can see the result of this process.
We've now combined the two tables into a single table using the Product ID.
This was actually a simple case, as the same Product IDs appeared in both tables. Things can get more complicated when some Product IDs appear only in one table.
Consider this situation.
In the Product Info table, we have a new product, KP005, that hasn't yet registered any sales.
In the Sales table, we have a product KP006 that hasn't been recorded in the Product Info table.
When we merge these two tables, we need to decide which Product IDs should be included in the combined table.
We can do this by considering various different join types.
First, we'll look at a full outer join.
This includes all data from both of our original tables.
As we can see, this table includes all the Product IDs from both tables, but creates some null values where a Product ID appears in only one of the tables.
Next, let's look at the inner join.
With this join, we include only Product IDs that feature in both tables.
Here we can see that the products which only appear in one table have been excluded.
As a result, no null fields are created as part of an inner join.
Next we'll look at a left join.
The left join includes all the Product IDs from the left data set, which in this case is the sales data set.
Product IDs which only appear in the product info data are not included in this join.
Finally, we'll look at the left join without intersection.
This is similar to the left join we saw before, but excludes the Product IDs that appear in both tables.
The result of this join is the data from the sales table does not have a corresponding Product ID in the Product Info table. In addition to the left joins we've seen here, you can also use a right join and a right join without intersection.
These follow exactly the same principles.
We've now learned how to join database tables together.
As we've seen, there are numerous different ways of combining two tables in a database.
The right method to use depends on the structure of the two tables you're combining as well as the desired output.
In the next and final lesson of this course, we'll learn how to create and understand diagrams of your database.