Joins & Table Relationships

Databases use relationships to connect tables via foreign keys. A JOIN combines rows from two or more tables based on a related column. The most common type is INNER JOIN, which returns only matching rows. This is how you link students to their enrolled courses, orders to customers, or projects to teams.

• A primary key (e.g., student_id) uniquely identifies each row in a table.
• A foreign key (e.g., student_id in the enrollments table) references the primary key of another table.
• INNER JOIN returns only rows with matching keys in both tables.
• LEFT JOIN returns all rows from the left table, even if no match exists on the right.

💡 Practical Skill: Design your database on paper first — draw Entity-Relationship Diagrams (ERDs) using tools like draw.io or MySQL Workbench. A clear schema prevents data duplication and keeps your queries fast.

-- Create a related enrollments tableCREATE TABLE enrollments (    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,    student_id INT,    course VARCHAR(100),    enrollment_date DATE,    FOREIGN KEY (student_id) REFERENCES students(id)); -- Insert enrollment dataINSERT INTO enrollments (student_id, course, enrollment_date)VALUES     (1, 'Database Management', '2026-01-15'),    (1, 'SQL Advanced', '2026-02-01'),    (2, 'Web Development', '2026-01-20'),    (3, 'Data Science', '2026-01-25'); -- INNER JOIN: show student names with their coursesSELECT s.name, e.course, e.enrollment_dateFROM students sINNER JOIN enrollments e ON s.id = e.student_idORDER BY s.name;-- Output: Grace (Database Management, 2026-01-15)-- Output: Grace (SQL Advanced, 2026-02-01)-- Output: John (Web Development, 2026-01-20)-- Output: Amina (Data Science, 2026-01-25) -- LEFT JOIN: show ALL students even if not enrolledSELECT s.name, e.courseFROM students sLEFT JOIN enrollments e ON s.id = e.student_id;