Skip to content

Lecture 24: Combining Tables with INNER JOIN

Combining Tables with INNER JOIN

This lecture covers the INNER JOIN to combine data from multiple tables. We have created separate `Customers` and `Orders` tables and linked them with a foreign key. But how do we see the customer’s name next to their order information in a single query? The answer is a JOIN clause.

A JOIN clause is used to combine rows from two or more tables based on a related column between them. The most common type of join is the INNER JOIN.

What is an `INNER JOIN`?

The INNER JOIN keyword selects records that have matching values in both tables. It effectively creates a new, temporary table that includes columns from both of the joined tables.

If a row in one table does not have a corresponding match in the other table, it will be excluded from the result. For our `Customers` and `Orders` tables, this means that a customer who has not placed any orders will not appear in the result of an inner join.

The `INNER JOIN` Syntax

Let’s write a query to get a list of all orders and include the first name of the customer who placed the order.

SELECT
    Orders.OrderID,
    Orders.OrderDate,
    Customers.FirstName
FROM Orders
INNER JOIN Customers ON Orders.Customer_ID = Customers.CustomerID;
Combining Tables with INNER JOIN
Combining Tables with INNER JOIN

Let’s break down this important syntax:

  • SELECT Orders.OrderID, Orders.OrderDate, Customers.FirstName: We are selecting columns from both tables. Notice that we have to specify which table each column comes from using the TableName.ColumnName syntax. This is important to avoid ambiguity if both tables had a column with the same name.
  • FROM Orders: We start from our “left” table, `Orders`.
  • INNER JOIN Customers: We specify that we want to join it with our “right” table, `Customers`.
  • ON Orders.Customer_ID = Customers.CustomerID: This is the crucial join condition. It tells the database how to match the rows. It says, “Match the rows where the value of the `Customer_ID` column in the `Orders` table is equal to the value of the `CustomerID` column in the `Customers` table.”

The result of this query is a unified list showing order information alongside the name of the customer who made it.

In the Next Lecture…

The `INNER JOIN` only shows results where a match exists in both tables. But what if we want to see all customers, including those who haven’t placed an order yet? For that, we need a different kind of join. In the next lecture, we will learn about the LEFT JOIN.

Lecture 23: Linking Tables with Foreign Keys

Tags: