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;
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 theTableName.ColumnNamesyntax. 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.
