Using INNER JOIN

Now that we understand how primary and foreign keys create relationships between tables, we can explore how to combine data from these related tables. The most frequent and fundamental way to do this in SQL is by using the INNER JOIN clause.

Think of INNER JOIN as a way to merge two tables horizontally based on a common column. It examines rows from both tables you specify and selects only those pairs of rows where the values in the designated "join columns" match exactly. If a row in one table doesn't have a corresponding match in the other table based on this join condition, it won't appear in the final result set.

The INNER JOIN Syntax

The basic structure for using INNER JOIN looks like this:

SELECT table1.column1, table1.column2, table2.columnA, table2.columnB
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Let's break down the components:

1. SELECT table1.column1, table2.columnA ...: You specify the columns you want to see in the final result. Notice that when columns have the same name in both tables (like a common ID column often does), or just for clarity, you need to prefix the column name with the table name (table1.column or table2.column). We'll see a way to shorten table names using aliases later.
2. FROM table1: This indicates the first table in your join (often considered the "left" table).
3. INNER JOIN table2: This specifies the second table you want to join (the "right" table) and the type of join (INNER).
4. ON table1.common_column = table2.common_column: This is the join condition. It's the most important part, telling the database how the tables are related. You specify which column in table1 should match which column in table2. Typically, this involves matching the primary key of one table with the corresponding foreign key in the other table.

An Example: Customers and Orders

Imagine we have two tables in our database: Customers and Orders.

Customers Table:

customer_id	first_name	last_name	city
1	Clara	Oswald	London
2	Amy	Pond	Leadworth
3	Donna	Noble	London
4	Martha	Jones	London

Orders Table:

order_id	product	quantity	customer_id
101	Screwdriver	1	2
102	TARDIS Key	1	1
103	Celery	5	5
104	Scarf	1	2

The Customers table uses customer_id as its primary key. The Orders table uses customer_id as a foreign key to link back to the Customers table.

Now, let's say we want to see a list of orders along with the name of the customer who placed the order. We need information from both tables. We can achieve this using INNER JOIN:

SELECT
    O.order_id,
    O.product,
    C.first_name,
    C.last_name
FROM
    Orders AS O
INNER JOIN
    Customers AS C
ON
    O.customer_id = C.customer_id;

• We select the order_id and product from the Orders table (using O as a shorthand alias for Orders).
• We select the first_name and last_name from the Customers table (using C as an alias for Customers).
• We start FROM the Orders table.
• We INNER JOIN it with the Customers table.
• The crucial ON clause specifies that the link between the tables is where the customer_id in the Orders table (O.customer_id) matches the customer_id in the Customers table (C.customer_id).

Understanding the Result

Running this query would produce the following result:

order_id	product	first_name	last_name
101	Screwdriver	Amy	Pond
102	TARDIS Key	Clara	Oswald
104	Scarf	Amy	Pond

Notice a few things:

1. Matching Rows Included: Order 101 is linked to customer 2 (Amy Pond), Order 102 to customer 1 (Clara Oswald), and Order 104 back to customer 2 (Amy Pond). All these appear because a matching customer_id was found in both the Orders and Customers tables.
2. Non-Matching Rows Excluded:
   • Donna Noble (customer 3) and Martha Jones (customer 4) are not in the result because they do not have any corresponding orders in the Orders table.
   • Order 103 is not in the result because its customer_id (5) does not exist in the Customers table.

This exclusion of non-matching rows is the defining characteristic of an INNER JOIN. It only returns rows where there's a connection based on the ON condition.

The diagram illustrates the INNER JOIN. Lines connect rows where customer_id matches between Customers and Orders. Only these connected rows form the final result set. Customer 3, Customer 4, and Order 103 are excluded because they lack a match in the other table.

INNER JOIN is the workhorse for combining related data. Mastering its syntax and understanding how it matches rows based on the ON condition is fundamental for querying relational databases effectively. In the following sections, we'll look at joining more than two tables and using aliases to make our queries cleaner.