Lesson 1
Learning SQL Joins with Online Shopping
Introduction and Expanding the Horizon

Welcome! I'm thrilled to have you all on board for this exciting journey — Learning SQL Joins. This course transforms the complexities of an online shopping dataset into a unique, engaging learning experience, bringing the excitement of data analysis to your fingertips.

We are about to dive into the fascinating world of SQL JOINs, linking tables together in complex but meaningful ways, much like a skilled craftsman intricately assembles different parts into a cohesive masterpiece.

Our tool of choice for this course is MySQL, a standard in database management systems. However, if you plan to use a different SQL-based system, rest assured — the concept of JOINs is universal across all platforms.

Brief Dataset Description

Let’s get acquainted with our dataset, inspired by the dynamics of an online shopping platform, which contains five primary tables. Below are some sample rows to give you an idea about the structure starting from the simplest table:

Categories Table

category_idcategory_name
1Flashcards
2Worksheets
3Guides
4Podcasts
5Courses

This table lists the five product categories available on the platform.

Customers Table

customer_idcustomer_name
1John Doe
2Jane Smith

This table provides details about the customers using the platform.

Products Table

product_idproduct_nameproduct_pricecategory_id
1Vocabulary Flashcards9.341
2Math Problems Flashcards12.841

The Products table contains detailed information about the products offered, including names, prices, and categories.

Orders Table

order_idcustomer_idorder_dateorder_status
1412021-08-17Delivered
2162022-04-03Processed

This table displays the orders made on the platform, along with their status and associated customers.

OrderItems Table:

order_item_idorder_idproduct_idextended_support
11250
22120

The OrderItems table includes information about each item in an order, such as the order it belongs to, the product, and whether it has support.

What are SQL JOINs?

SQL JOINs are techniques to combine data from two or more tables based on a shared column between them. They help in extracting valuable information that might be distributed across multiple tables. Several types of JOINs provide flexibility in how we choose to connect and derive insights from this data.

Mastering JOIN Relationships

When performing a JOIN between two tables, it’s essential to understand the relationship between the columns involved. Functional dependencies arise when one column in a table uniquely determines another column. If these dependencies are not considered, it can lead to incorrect data interpretation or unnecessary duplication in the result set. We must ensure that the columns used in the ON clause of the JOIN define a clear relationship between the tables. In the Products table, category_id uniquely determines the category_name from the Categories table. A JOIN between these tables should respect this dependency to avoid redundant or conflicting data.

Without a valid ON condition, a JOIN can result in a Cartesian product, where every row from one table is paired with every row from another table. This can lead to an exponential increase in the result set size. For instance:

SQL
1SELECT * FROM Customers, Orders;

This query retrieves all possible combinations of rows from Customers and Orders, potentially resulting in millions of rows for large tables. Proper use of the ON condition helps prevent such excessive results by ensuring meaningful connections between tables.

INNER JOIN

Before we deep-dive into the world of SQL JOINs, it's crucial to understand the variety of joins available. Each type serves a unique purpose, allowing us to adjust our queries to retrieve the exact data we need.

An INNER JOIN, often referred to as just JOIN, is the most common type. It fetches records with matching values in both participating tables. Unmatched rows are not included in the result. By default, an INNER JOIN is assumed if no specific type of JOIN is mentioned.

Example:

SQL
1SELECT Orders.order_id, Orders.order_date, Orders.order_status, Customers.customer_name AS Customer 2FROM Orders 3JOIN Customers ON Orders.customer_id = Customers.customer_id 4WHERE Orders.order_status = 'Canceled' 5ORDER BY Orders.order_date DESC;

In this example, we use INNER JOIN to combine the Orders and Customers tables. The query retrieves the order ID, date, and customer name for orders that have been canceled, ordered by the date of the order in descending order.

LEFT, RIGHT, and FULL JOIN

A LEFT JOIN (also known as LEFT OUTER JOIN) provides all records from the left table and the matched records from the right table. If there's no match, the result set will include NULL values for the right table's columns. This JOIN is advantageous when you want to include all records of one table (the left one) regardless of matching rows in the other table.

Alternatively, a RIGHT JOIN (or RIGHT OUTER JOIN) offers all records from the right table and the matched records from the left table. If there's no match, the result set will include NULL values for unmatched left table's columns.

A FULL JOIN (or FULL OUTER JOIN) yields all records when a match exists in either of the participating tables. It combines the effects of both LEFT JOIN and RIGHT JOIN. If there's no match, the result set will have NULL values for every column of the table lacking a matching row. FULL JOIN is not directly supported in MySQL but can be emulated with a combination of LEFT JOIN, RIGHT JOIN, and UNION, which we'll explore later in this course.

Wrapping Up and Looking Ahead

As we conclude this introductory module on SQL JOINs, think of yourself as an artisan learning a new craft. Remember, understanding SQL JOINs theoretically sets the stage, but applying them hands-on is the real key to mastery.

Moving forward, we'll begin by delving into each table — Categories, Customers, Products, Orders, and OrderItems — individually. This approach will not only familiarize you with the data but also build a solid foundation for when we start interlinking these tables using JOINs. From simple data retrieval to intricate queries, you'll learn to weave the story of an online shopping platform using SQL.

Enjoy this lesson? Now it's time to practice with Cosmo!
Practice is how you turn knowledge into actual skills.