1. Course Introduction

Overview

In this lesson, we cover the structure of the course.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Course summary (00:26)

    In this course, we’ll approach joins in three separate stages. First, we’ll examine joins from a more conceptual perspective using a very simple dataset in order to easily convey the differences between joins.

    We’ll then move onto learning how each of these joins can be implemented in SQL code using our bicycle store database.

    Finally, we’ll explore more advanced joins such as the self join.

  2. Simple dataset (01:00)

    For the first part of our course, we’ll be using a simple dataset that comes in two tables.

    sale_id

    sale_date

    quantity

    product_id

    1

    12/01/2021

    2

    2

    2

    12/01/2021

    3

    1

    3

    13/01/2021

    1

    4

     

    The first is a simple sales transaction dataset with just three rows.

    product_id

    product_name

    price

    1

    fishing rod

    150.00

    2

    bait

    5.00

    3

    lure

    8.00

     

    The second is a simple product table, also with just three rows.

    However, note that in the sales table there’s a transaction where a product the id 4 was sold whereas there is no product with this id in the product table. Similarly, the product with the id 3 exists in the product table but it has no associated sales transactions.

    This disconnect means that there are many different types of forms that our query output can take and this will depend on the type of join we use.

Transcript

In the first course, we learned that when we select columns from different tables we need to join these different tables together. We've joined many tables since then, however we haven't expanded on the different ways that tables can be joined together, and how this affects the output. To that end, this course focuses on SQL joins through three separate sections.

In the first section, we'll visually conceptualize the different types of joins.

In the next section, we'll build these joins in SQL code.

In the final section, we'll explore more advanced joins that can be useful in very specific situations.

Later in this course, we'll use the bicycle store data set when building these joints with SQL code, but first, we'll use a new and very simple data set with two tables to help us visualize these joins.

The first is a simple sales transaction table which records five transactions from a fish and tackle store.

The second, is a simple product table which records information about five different products. If we look at our two tables, we can see that the product ID column appears in both. It's the primary key in the products table and the foreign key in the sales table. At this stage, it should be clear how this connects these two tables. But note that there's a problem. There's different information in the primary key and the foreign key.

The sales table doesn't show any sales transactions for the product with ID three.

This makes sense as perhaps that product just hasn't been sold yet.

However, the sales table shows a transaction for a product with the ID for, that's not in the product table.

Perhaps it's a brand new product, and our records haven't been updated yet. This begs the question of how this inconsistency is dealt with when we join these tables. The answer is that it depends on the type of join we use. Let's stop the lesson here. In the next lesson, we'll explore how these different types of joins, affect the output of our SQL query.

Joining and Aggregating Data
Joining Data with SQL

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial