Joining Related Datasets

Merge information from distinct tables to create a unified view of related entities.

In this lesson, you will learn how to combine information from two separate tables into a single, cohesive view to see the "big picture" of your data.

The Concept of Relationships

Up until now, you have mostly queried a single table. However, databases are typically organized into multiple tables to keep data clean and organized. For example, imagine you have a Customers table containing names and a Orders table containing purchase amounts. While the Orders table knows who placed an order, it usually only stores a customer ID number rather than the customer’s full name.

To get the customer's name next to their order, you need a way to connect these two tables. We do this by matching the ID found in the Orders table with the corresponding ID in the Customers table.

The Inner Join

The most common way to merge these tables is using the INNER JOIN keyword. This tells the database to look for rows that have a matching value in both tables.

Consider this query:

SELECT Customers.name, Orders.amount
FROM Customers
INNER JOIN Orders ON Customers.id = Orders.customer_id;

Here is what is happening step-by-step:

FROM Customers: We start with our primary table.
INNER JOIN Orders: We bring in the second table we want to attach.
ON Customers.id = Orders.customer_id: This is the "bridge." It tells SQL: "Only give me a row if the ID in the Customers table exactly matches the customer_id in the Orders table."

If a customer hasn't placed an order, they won't appear in this result because the join requires a match on both sides. This creates a "unified view" where you can see the person's name directly alongside the order details they generated, turning two isolated datasets into a single, meaningful report. By using the table name prefix (like Customers.name), you remove any confusion about which table your data is coming from, especially if both tables happen to share column names.

Key Takeaway: Joining allows you to build a unified view of your data by using shared identifiers as a bridge to link related information across multiple tables.