Exploring INNER JOIN with Online Shop's Product Data

Hello and welcome back! It's wonderful to see your eagerness to delve deeper into the realm of SQL JOINs. Previously, we explored various types of JOINs and navigated through the intricate tables of our online shopping database. In this lesson, we'll have an in-depth discussion of INNER JOIN.

In this session, we'll focus on the INNER JOIN. Essentially, the INNER JOIN in SQL is a clause that merges rows from two tables based on a shared column between them. The result is a dataset that includes only the rows that satisfy the joining condition.

Before diving into examples, let's review the standard syntax for executing an INNER JOIN:

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

This syntax serves as the foundation of our queries whenever we desire to merge data from two related tables. In this lesson, we'll see how this structure is practically used to join Products and OrderItems tables.

By the end of this lesson, you'll be adept at retrieving data from the OrderItems and Products tables using INNER JOIN. The key product_id will serve as the common link between these tables.

Let's start with a basic example:

Suppose we have an OrderItems table showcasing the extended_support option for orders. This can be linked to the Products table, where we want to retrieve product details. We can use an INNER JOIN to unify these two tables:

SELECT Products.product_name, Products.product_price, OrderItems.extended_support
FROM Products
INNER JOIN OrderItems
ON Products.product_id = OrderItems.product_id;

-- Sneak peek of the output:
-- | product_name                   | product_price | extended_support |
-- |--------------------------------|---------------|------------------|
-- | Critical Thinking Guide        |         16.15 |                0 |
-- | Grammar Exercises Worksheets   |         10.70 |                0 |

In this SQL statement:

• SELECT Products.product_name, Products.product_price, OrderItems.extended_support specifies the data we want — product_name and product_price from the Products table and extended_support from the OrderItems table.
• FROM Products indicates the primary table from which we start our query.
• INNER JOIN OrderItems indicates that we wish to connect the Products table with the OrderItems table.
• ON Products.product_id = OrderItems.product_id is the key condition that joins these tables. It ensures that the join happens through the product_id column, common to both tables. This condition aligns the rows from Products and OrderItems accurately.

Executing this INNER JOIN query on the Products and OrderItems tables generates a comprehensive list that associates each product with its respective ordering details, showcasing the utility of INNER JOIN to proficiently merge related data from two tables.

Note: If a product is ordered multiple times, the INNER JOIN will duplicate the product details for each matching row in the OrderItems table. For example, when retrieving product names and details, you might see the same product listed multiple times if it appears in several order entries with different support options. To handle this, you can use aggregation with GROUP BY to summarize the data and eliminate duplicates, grouping the results by product and providing a count of the order items. This allows for an effective summary of the data without repetition.