Phase 4: Databases & SQL for AI/ML · ~40 minutes · SQL · SQLite

Joins and Building Training Datasets

4 customers, 9 orders, one customer with zero orders. INNER JOIN quietly returns a dataset with that customer nowhere in it at all — no error, no warning, just gone.

Hiring signal: Checks row counts before and after every join, on reflex, specifically to catch silent drops before they reach a training set

What you will learn

Use INNER, LEFT, and FULL joins correctly
Explain why an inner join silently dropping unmatched rows matters specifically for training data
Build a single flat table from multiple related tables for ML use
Predict the resulting row count from two tables and a join type

Introduction

Type: Learn Languages: SQL Prerequisites: Lesson 02 (SQL: Getting Data Out) Time: ~40 minutes

Objective

Learning objectives

Use INNER, LEFT, and FULL joins correctly
Explain why an inner join silently dropping unmatched rows matters specifically for training data
Build a single flat table from multiple related tables for ML use
Predict the resulting row count from two tables and a join type

What you're building

Using this lesson's customers/orders tables (or your own with a similar one-to-many relationship, plus a third related table if you'd like a real 3-table join):

Build a flat table with INNER JOIN, record the row count, and identify by name which customer(s) it silently excludes
Build the same flat table with LEFT JOIN, record the row count, and confirm it includes every customer, including those with NULL order fields
Write a query specifically finding customers with zero orders, using IS NULL on a joined column, per this lesson's pattern
Write join_verification.txt stating the exact row counts for customers, orders, the inner-joined result, and the left-joined result, with a one-sentence explanation of why the two join results differ by exactly the number they do

Table products has 6 rows. Table reviews has 20 rows, all of which reference a product that exists in products, except none of products' 6 rows has zero reviews — every product has at least one. What will COUNT(*) return for an INNER JOIN between them?

An INNER JOIN produces one output row for each MATCHING pair between the two tables — not one row per table, and not a sum. Since every one of the 20 reviews references a product that genuinely exists in the 6-row products table, all 20 reviews find a match and survive the join, each paired with its product's data. The result has 20 rows — the count is driven by how many rows on the 'many' side (reviews) have a match, not by the smaller table's row count. (If some products had zero reviews, they simply wouldn't appear in this INNER JOIN result at all, per this lesson's Katherine example — but that's not the case described here.)

Unlock the full lesson

You've read the first 2 sections. The rest of this lesson covers The Problem, Check Yourself, Key Terms & Next — plus a hands-on lab, quiz, and project artifact.

Create a free account to unlock Phase 0 and Phase 1 of every course — no credit card.