Hands-on Practical: Writing INNER JOIN Queries

Okay, let's put what we've learned about INNER JOIN into practice. These exercises will help solidify your understanding of how to combine data from related tables. We'll use a small, hypothetical database for an online store.

Sample Database Schema

Imagine we have three tables: Customers, Orders, and Products.

1. Customers Table (Customers)

This table stores information about registered customers.

customer_id (PK)	first_name	last_name	email	registration_date
1	Alice	Smith	alice@example.com	2023-01-15
2	Bob	Johnson	bob.j@example.net	2023-02-20
3	Charlie	Davis	charlie.d@mail.org	2023-01-10
4	Diana	Brown	diana@sample.com	2023-03-05

• customer_id is the Primary Key (PK) for this table.

2. Orders Table (Orders)

This table logs customer orders.

order_id (PK)	customer_id (FK)	product_id (FK)	order_date	quantity
101	1	501	2023-03-10	1
102	3	502	2023-03-12	2
103	1	503	2023-03-15	1
104	2	501	2023-03-16	3
105	4	504	2023-03-18	1
106	3	501	2023-03-20	1

• order_id is the Primary Key (PK).
• customer_id is a Foreign Key (FK) referencing Customers(customer_id).
• product_id is a Foreign Key (FK) referencing Products(product_id).

3. Products Table (Products)

This table lists the products available.

product_id (PK)	product_name	category	price
501	Laptop	Tech	1200
502	Keyboard	Tech	75
503	Coffee Mug	Home	15
504	Desk Lamp	Home	45
505	Wireless Mouse	Tech	25

• product_id is the Primary Key (PK).

Here's a visual representation of how these tables relate:

Relationship diagram showing Customers and Products tables linked to the Orders table via foreign keys customer_id and product_id respectively.

Now, let's write some queries!

Exercise 1: Get Customer Names and Order Dates

Goal: Retrieve a list showing the first name of each customer and the date of each order they placed.

Approach: We need data from Customers (first name) and Orders (order date). These tables are linked by customer_id. We'll use an INNER JOIN on this common column.

Query:

SELECT
    Customers.first_name,
    Orders.order_date
FROM
    Customers
INNER JOIN
    Orders ON Customers.customer_id = Orders.customer_id;

Explanation:

• SELECT Customers.first_name, Orders.order_date: Specifies the columns we want from each table. We use TableName.ColumnName syntax to be explicit, especially when column names might exist in both tables (though not the case here for first_name and order_date).
• FROM Customers: Specifies the first table.
• INNER JOIN Orders: Specifies the second table we want to join.
• ON Customers.customer_id = Orders.customer_id: This is the join condition. It tells the database to match rows where the customer_id in the Customers table is the same as the customer_id in the Orders table. Only matching rows will be included in the result.

Expected Output:

first_name	order_date
Alice	2023-03-10
Charlie	2023-03-12
Alice	2023-03-15
Bob	2023-03-16
Diana	2023-03-18
Charlie	2023-03-20

Exercise 2: Using Table Aliases

Goal: Get the order_id, the customer's email, and the order_date. Use table aliases for brevity. Rename the email column to customer_email in the output.

Approach: We join Customers and Orders again. This time, we'll use short aliases (c for Customers, o for Orders) to make the query easier to read and write. We also use a column alias for email.

Query:

SELECT
    o.order_id,
    c.email AS customer_email,
    o.order_date
FROM
    Customers AS c
INNER JOIN
    Orders AS o ON c.customer_id = o.customer_id;

Explanation:

• Customers AS c: Assigns the alias c to the Customers table.
• Orders AS o: Assigns the alias o to the Orders table.
• o.order_id, c.email, o.order_date: We use the aliases (c. and o.) to refer to columns instead of the full table names.
• c.email AS customer_email: Renames the email column to customer_email in the result set using a column alias.
• ON c.customer_id = o.customer_id: The join condition now uses the table aliases.

Expected Output:

order_id	customer_email	order_date
101	alice@example.com	2023-03-10
102	charlie.d@mail.org	2023-03-12
103	alice@example.com	2023-03-15
104	bob.j@example.net	2023-03-16
105	diana@sample.com	2023-03-18
106	charlie.d@mail.org	2023-03-20

Exercise 3: Joining Three Tables

Goal: Get a combined list showing the customer's first name, the name of the product ordered, and the quantity ordered.

Approach: We need information from all three tables: Customers (first name), Products (product name), and Orders (quantity). We need two INNER JOIN clauses: one to link Customers to Orders (using customer_id), and another to link Orders to Products (using product_id). We'll use table aliases again.

Query:

SELECT
    c.first_name,
    p.product_name,
    o.quantity
FROM
    Customers AS c
INNER JOIN
    Orders AS o ON c.customer_id = o.customer_id
INNER JOIN
    Products AS p ON o.product_id = p.product_id;

Explanation:

• We start by joining Customers (c) and Orders (o) on customer_id.
• Then, we add another INNER JOIN to bring in the Products table (p).
• The second ON clause (o.product_id = p.product_id) links the result of the first join (which includes o.product_id) to the Products table based on the product_id.
• The SELECT list pulls columns from all three aliased tables (c, p, o).

Expected Output:

first_name	product_name	quantity
Alice	Laptop	1
Charlie	Keyboard	2
Alice	Coffee Mug	1
Bob	Laptop	3
Diana	Desk Lamp	1
Charlie	Laptop	1

Exercise 4: Combining JOIN with WHERE and ORDER BY

Goal: Find the order_id and product_name for all orders placed by the customer with customer_id = 1 (Alice). Sort the results by order_id.

Approach: We need to join Orders and Products to get the product name associated with each order. Then, we filter the results using a WHERE clause to only include orders from customer_id = 1. Finally, we sort using ORDER BY.

Query:

SELECT
    o.order_id,
    p.product_name
FROM
    Orders AS o
INNER JOIN
    Products AS p ON o.product_id = p.product_id
WHERE
    o.customer_id = 1
ORDER BY
    o.order_id;

Explanation:

• We first INNER JOIN Orders (o) and Products (p) on product_id.
• WHERE o.customer_id = 1: This clause filters the joined results, keeping only the rows where the customer_id in the Orders table is 1. Note that we could also use WHERE c.customer_id = 1 if we had also joined the Customers table, but since customer_id is already in the Orders table, joining Customers isn't strictly necessary for this specific query goal.
• ORDER BY o.order_id: Sorts the final filtered result set based on the order_id in ascending order (which is the default).

Expected Output:

order_id	product_name
101	Laptop
103	Coffee Mug

These practical examples demonstrate how INNER JOIN allows you to weave together related information scattered across different tables. By mastering joins, specifying join conditions correctly using primary and foreign keys, and utilizing aliases, you can construct powerful queries to extract meaningful insights from relational databases. Try modifying these queries or creating your own based on the sample data to further explore combining tables.