INNER JOIN: Combining Data from Multiple Tables

Why Do We Need to Join Tables?

Before we dive into the syntax, it's crucial to understand why we need joins in the first place. The practice of splitting data into separate, specialized tables is called normalization. It's a core database design principle that avoids data duplication and ensures data integrity. For a Business Analyst, understanding this concept is important because it explains the structure of the data you'll be working with.

To get a clear, non-technical explanation of this concept, let's start with a short video.

As the video explains, tables are connected by common columns. Typically, this involves a Primary Key (a unique identifier for a row in one table, like country_id in a countries table) and a Foreign Key (the same identifier used in another table to refer back to the first one, like country_id in a states table). The JOIN clause uses this key relationship to match and combine rows.

How INNER JOIN Works

There are several types of joins, but they all serve to combine tables. The INNER JOIN is the most frequently used. It returns only the rows where the key exists in both tables.

Let's visualize how the database performs this matching process.

Writing Your First INNER JOIN

Now that you understand the concept, let's look at the syntax. An INNER JOIN query has four main parts:

1. SELECT: The columns you want to display.
2. FROM: The first (or "left") table.
3. INNER JOIN: The second (or "right") table you want to connect.
4. ON: The condition that tells SQL how to match the rows, usually by equating the primary and foreign keys.

SELECT
  table1.column_name,
  table2.column_name
FROM
  table1
INNER JOIN
  table2 ON table1.common_id = table2.common_id;

Notice that we specify the table for each column (table1.column_name). This is crucial to avoid ambiguity if columns in both tables have the same name (like id or name).

Let's watch a step-by-step guide to writing this query.

Using aliases (AS) is standard professional practice. It dramatically improves query readability, especially when joining multiple tables. From now on, you should always use aliases in your joins.

Here is the previous query rewritten with aliases:

SELECT
  t1.column_name,
  t2.column_name
FROM
  table1 AS t1
INNER JOIN
  table2 AS t2 ON t1.common_id = t2.common_id;

SELECT
  e.employee_name,
  d.dept_name
FROM
  employees AS e
INNER JOIN
  departments AS d ON e.department_id = d.dept_id;

Practical Application: Analyzing Ad Campaign Performance

Let's apply this to a scenario that reflects both your background in AdTech and the kind of tasks you'll perform in a BA interview. A common request is to measure advertising effectiveness by calculating metrics like Click-Through Rate (CTR). This almost always requires joining data from multiple tables.

The following resource presents a typical problem from a company like The Trade Desk.

This example is an excellent showcase of a real-world BA task. You start with a business question, identify the necessary data points, figure out how the tables connect, and then write a query to produce the result. The query joins three tables to link campaigns to regions and impression events, then uses the GROUP BY and AVG() skills you already have to calculate the final metric.

This combination of JOIN with aggregations is one of the most powerful patterns in SQL for business analysis.

Conclusion

Congratulations on taking a huge step forward in your SQL journey! You've moved beyond single-table analysis and can now connect and query related datasets. This is a fundamental skill for any data-facing role, especially a Business Analyst.

Key Takeaways:

• Data is often split into multiple tables (normalization) to maintain organization and integrity.
• INNER JOIN is used to combine rows from two or more tables based on a matching key in both tables.
• The query structure is ... FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.key = t2.key.
• Using table aliases (AS) is a professional best practice that makes queries cleaner and easier to read.
• Combining JOIN with GROUP BY and aggregate functions allows you to answer complex business questions that span multiple datasets.

Preview of the Next Lesson:
INNER JOIN is perfect for finding matching data. But what if you want to find mismatches? For example, "Show me all customers, and if they have placed an order, show the order details." An INNER JOIN would exclude customers who haven't ordered. To handle this, we need an outer join. In the next lesson, we will explore the LEFT JOIN, which allows you to keep all records from the "left" table, regardless of whether they have a match in the right table.