Select data from multiple tables with JOIN

OVERVIEW

Introduction

As you may recall, primary keys are used to establish relationships between tables. Take a look at the tables below, which store customer data (Customers) and their invoices (Invoices).

Customers
Customers
Invoices
Invoices

The Invoices table does not store the customer’s name or address. In the Invoices table, we only use the CustomerId to refer to a specific customer in the Customers table. The CustomerId column in the Invoices table is called a foreign key . A foreign key references a primary key in another table (Customers).

In this lesson, we’ll look at how to combine data from both tables into a single result. In this lesson, we want to create a list containing the CustomerId, CustomerName, and InvoiceAmount fields from both tables. We also want to sort this list by CustomerId.

To implement this query, we use a JOIN clause. There are several types of JOIN clauses available. In this course, we will focus on the INNER JOIN. This is the most useful type of JOIN.

INNER JOIN

The general syntax is as follows:

SELECT table_name1.column_list, table_name2.column_list
FROM table_name1
INNER JOIN table_name2
ON table_name1.primary_key = table_name2.foreign_key;
  • The SELECT statement is followed by a list of column names that appear in one of the tables. To avoid confusion between identical column names, the name of the relevant table is prepended to the column name: Customers.CustomerId, Customers.CustomerName, Invoices.InvoiceAmount .
  • The FROM keyword is followed by the name of the table (Customers) containing the primary key;
  • The INNER JOIN clause is followed by the name of the table containing the foreign key (Invoices);
  • After the keyword ON, the relationship is specified to indicate when the data should be included in the result (Customers.CustomerId = Invoices.CustomerId).

This is the complete SQL statement:

SELECT Customers.CustomerId, Customers.CustomerName, Invoices.InvoiceAmount
FROM Customers
INNER JOIN Invoices
ON Customers.CustomerID = Invoices.CustomerId
ORDER BY Customers.CustomerId;
INNER JOIN
INNER JOIN

To calculate the total invoice amount per customer, we can use the well-known GROUP BY clause:

SELECT Customers.CustomerId, Customers.CustomerName, SUM(Invoices.InvoiceAmount)
FROM Customers
INNER JOIN Invoices
ON Customers.CustomerID = Invoices.CustomerId
GROUP BY Customers.CustomerId
ORDER BY Customers.CustomerId;
Invoice amount per customer
Total invoice amount per customer