This directory contains the curriculum, exercises, and practice scripts for Table Joins.
- 1. What is a Join and Types of Joins - Get the foundational definitions of joins, understand the PK-FK relational mapping mechanism, and see an overview classification of all join types.
- 2. Inner, Equi, and Non-Equi Joins - Learn the mechanics of standard INNER JOINs, the equality-based EQUI JOINs, and non-equality comparison JOINs (using
<,>,!=, orBETWEEN). - 3. Outer Joins - Left and Right Join - Master handling missing data with LEFT and RIGHT joins, null padding, and the
IS NULLsearch pattern for orphaned data. - 4. Full Outer and Natural Joins - Emulate FULL OUTER JOIN using UNION in MySQL, and understand implicit NATURAL JOIN columns.
- 5. Self and Cross Joins - Join a table to itself for hierarchies (Self Join) and create Cartesian products (Cross Join).
- 6. Join Optimization and Performance - Deep dive into database internals: Nested Loops vs. Hash Joins (MySQL 8.0+), indexing join keys, and forcing orders with
STRAIGHT_JOIN. - 7. Hands-on Practice and Exercises - Test your learning with e-commerce exercises, including non-equi joins and multi-table joins.
- day4_practice_script.sql - Executable SQL file to practice all Join operations hands-on.