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:

SQL
Copy to clipboard
1SELECT column1, column2, ...
2FROM table1
3INNER JOIN table2
4ON 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:

SQL
Copy to clipboard
1SELECT Products.product_name, Products.product_price, OrderItems.extended_support
2FROM Products
3INNER JOIN OrderItems
4ON Products.product_id = OrderItems.product_id;
5
6-- Sneak peek of the output:
7-- | product_name                   | product_price | extended_support |
8-- |--------------------------------|---------------|------------------|
9-- | Critical Thinking Guide        |         16.15 |                0 |
10-- | 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.

The INNER JOIN only includes rows where the join condition is satisfied. If a row in one table has a NULL value in the join column, it will not appear in the result set. For instance, if a product_id in the OrderItems table is NULL, that row will not match any row in the Products table and will be excluded from the results.

To find rows with NULL values that are excluded by the INNER JOIN, you can use a LEFT JOIN and filter for NULL in the right table’s column:

SQL
Copy to clipboard
1SELECT OrderItems.order_item_id, OrderItems.product_id
2FROM OrderItems
3LEFT JOIN Products ON OrderItems.product_id = Products.product_id
4WHERE Products.product_id IS NULL;

This query retrieves OrderItems that do not have a matching product_id in the Products table.

Let's refine our example by arranging all products along with their order support status in descending order of product price. Here's how the query is structured:

To solve this, we can use aggregation (GROUP BY) to summarize data and eliminate duplicates:

This approach leverages GROUP BY to group the results by product-wise, providing a count of the order items, thereby summarizing the data effectively.

SQL
Copy to clipboard
1SELECT Products.product_name, Products.product_price, OrderItems.extended_support
2FROM Products
3INNER JOIN OrderItems
4ON Products.product_id = OrderItems.product_id
5ORDER BY Products.product_price DESC;
6
7-- Example output:
8-- | product_name                   | product_price | extended_support |
9-- |--------------------------------|---------------|------------------|
10-- | Project Management Course      |        149.26 |                0 |
11-- | Project Management Course      |        149.26 |                0 |

In this scenario, the ORDER BY Products.product_price DESC condition arranges the results in descending order by product price.

Congratulations! You've covered significant ground in this lesson. You've mastered the INNER JOIN instruction to extract specific data from two linked tables. This SQL tool is invaluable in uncovering intricate associations within databases. Keep practicing, and soon you'll be constructing SQL statements with ease!