Overview
SQL Joins & Advanced
Group Filtering Practice
Combining related business tables and filtering linked analytical results — the multi-table SQL skills that drive real-world analytics reporting.
Course Learning Objectives
- Use JOIN…ON to combine two related tables using a shared key column, applying table.column notation for unambiguous column references.
- Use INNER JOIN to retrieve only records that have matching values in both tables, excluding unmatched rows from either side.
- Use LEFT JOIN to preserve all records from the left table including those with no match in the right table, returning NULL in right-table columns for unmatched rows.
- Use RIGHT JOIN to preserve all records from the right table including those with no match in the left table, returning NULL in left-table columns for unmatched rows.
- Simulate FULL JOIN in MySQL using LEFT JOIN UNION RIGHT JOIN to return all records from both tables — matched and unmatched — for complete relationship visibility.
Part 1 introduces SQL JOINs — the mechanism that connects related tables in a relational database. Students learn the JOIN…ON structure, why table.column notation is required, how shared key columns link tables, and apply the pattern across users, transactions, orders, products, subscriptions, and sessions.
The JOIN…ON structure and how it merges rows from two related tables using a shared column.
Primary keys, foreign keys, and the key relationships in the course database.
Connecting users to transactions, products to order items, and users to subscriptions.
Why JOIN is the most important SQL skill for professional analytical work.
- Practice focus: joining users to transactions, orders, subscriptions, tickets, campaign events, and sessions; and joining products to order items
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 2 covers INNER JOIN — the join type that returns only rows with matching values in both tables. Students learn why INNER JOIN excludes unmatched rows, why it produces no NULL values from the join itself, and that it is identical to a plain JOIN in MySQL.
How INNER JOIN returns only the intersection — rows where both tables have a corresponding value.
Analyzing established relationships where both sides of the link must exist.
Products with sales, users with orders, customers with subscriptions — all requiring both sides.
Joining user profiles to transaction amounts for financial reporting with no NULLs.
- Practice focus: INNER JOIN across users-transactions, users-orders, products-order_items, users-subscriptions, users-tickets, users-campaign_events, and users-sessions
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 3 covers LEFT JOIN — the join type that preserves all left-table rows. Students learn that unmatched left rows appear with NULL in right-table columns, how to use this for gap detection, and when to choose LEFT JOIN over INNER JOIN to preserve the complete left-table list.
Every left-table row appears regardless of whether a matching row exists in the right table.
Rows with NULL in right-table columns reveal customers without orders, products without sales.
Using LEFT JOIN to surface operational gaps — users without subscriptions, tickets without users.
LEFT JOIN when the right-side relationship may not always exist for every left-table record.
- Practice focus: LEFT JOIN preserving all users, all products, and all orders alongside optional related data across the database
- 8 practice queries — each with an AI prompt and a validated SQL answer including a multi-table chain join for orders → order_items → products
Part 4 covers RIGHT JOIN — the mirror of LEFT JOIN, preserving all right-table rows. Students learn how RIGHT JOIN surfaces orphaned records, how any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping table order, and when to use RIGHT JOIN for completeness checks on operational records.
Every right-table row appears regardless of whether a matching row exists in the left table.
Analyzing business relationships from the right-table perspective — all orders, all subscriptions.
Identifying orphaned right-table records with NULL in left-table columns after the join.
Preserving complete operational tables (transactions, order_items) as the primary reporting subject.
- Practice focus: RIGHT JOIN preserving all transactions, orders, order_items, subscriptions, tickets, campaign_events, and sessions
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 5 covers FULL JOIN — the most inclusive join type. Students learn that MySQL does not support FULL JOIN natively, how to simulate it using LEFT JOIN UNION RIGHT JOIN, and how UNION removes duplicate rows to produce the full outer join result.
FULL JOIN returns matched rows plus all unmatched rows from each table with NULLs.
All users, all orders, all subscriptions visible simultaneously — matched and unmatched.
Gaps on both sides visible at once — the complete reconciliation picture.
Data quality audits and completeness reports requiring all records from both datasets.
- Practice focus: simulating FULL JOIN using LEFT JOIN UNION RIGHT JOIN across all major table pairs in the database
- 8 practice queries — each with an AI prompt and a validated SQL answer using the UNION pattern
Course at a Glance
Five parts · 40 practice queries · same tables as Parts 1–4
| Part | JOIN Type | What It Returns | Practice |
|---|---|---|---|
| Part 1 | JOIN (Introduction) | Introduces the JOIN…ON structure and table.column notation for multi-table queries | 8 Queries |
| Part 2 | INNER JOIN | Only rows with matching values in both tables — no unmatched rows, no NULLs from the join | 8 Queries |
| Part 3 | LEFT JOIN | All left-table rows plus matching right rows — unmatched left rows show NULL in right columns | 8 Queries |
| Part 4 | RIGHT JOIN | All right-table rows plus matching left rows — unmatched right rows show NULL in left columns | 8 Queries |
| Part 5 | FULL JOIN (via UNION) | All rows from both tables — matched pairs, left-only rows, and right-only rows | 8 Queries |
Every practice question in this course includes a business question, an AI prompt for ChatGPT or Copilot, the validated SQL answer, and guidance on interpreting the result. Students learn to prompt effectively, validate JOIN logic, troubleshoot relationship errors, and connect multi-table output to real business decisions.
What You Leave With
Five JOIN types. Forty practice queries. A complete multi-table SQL toolkit.
The JOIN…ON structure and table.column notation for any multi-table query.
Matched-record retrieval — the strictest join type for confirmed business relationships.
Complete left-table lists alongside optional right-table data — including gap detection.
Complete right-table records for completeness checks and reverse relationship analysis.
Full outer join simulation via LEFT JOIN UNION RIGHT JOIN for complete visibility.
The ability to prompt, verify, and troubleshoot JOIN queries in a professional analytical workflow.
The foundation for relational analytics, BI dashboards, and business intelligence workflows.
Certificate of Completion from the Canadian Institute of Artificial Intelligence (CiAI), powered by DataSoSi.
