Simple SQL: How to Join Tables

We are starting off our newest blog series featuring SQL tutorials for those getting started. Keep posted for more Simple SQL posts.

If you have just started creating SQL queries, you’ve probably discovered that your database stores different types of data in different tables, such as basic user information in a User table, and orders or payments information in an Orders table.

While you may be able to pull useful information from a single table, often you will need to combine information from more than one table in order to answer questions about your data.

To combine tables you will have to JOIN them. This tutorial walks you through the steps to create a JOIN in SQL. In this example, we will answer the question - which customers have made the most purchases? We will be using our demo ecommerce database, and writing everything in SQL.

In our ecommerce database we will only need to look at our Users table and Orders tables.

Using a simple SELECT statement, we can generate a list of our customers from the Company column in the Users table.

The resulting table shows a list of company names - our customers.

Similarly, we can find out how much our customers spent from the Total Cost column in the Orders table.

And we get a table listing the amount spent by companies.

But as you can tell, these tables separately don’t provide very useful information. We need to link these two tables together so we can find out which companies spent the corresponding amount.

But how do we join the two tables? Looking back at the Users and the Orders tables, both have a column for User ID - we can use this relation to join the tables.

This relation is known as a foreign key - a column that references a column of another table. We use the below syntax to join the two tables on the foreign key column:

FROM table1
JOIN table2 ON table1.column_name = table2.column_name;

Now you know how to JOIN, but what is wrong with the resulting table? Each company has made several purchases, so there are many entries for each company. We want to sum and display the purchases made for each company.

To sum the Total_Cost column, we’ll use the SUM() function. To display the result for each company we will add a GROUP BY to the Company column so that the results will group by company name. Check out this resource for more on the GROUP BY function.

And we have a list of our top customers, with Ackerman Medical Supply Co being the biggest spender.

If you’d like to learn more about joining tables in SQL, and the different types of joins - check out our education article SQL JOINS Explained.

If you liked this blog post be sure to sign up to receive the latest on SQL and data from the Chartio blog.