Databases

Lesson 8 - JOIN

SQL Joins

Lesson Overview

Combine data from multiple tables using SQL joins.

Understanding SQL Joins

A JOIN clause is used to combine rows from two or more tables based on a related column between them. Common types of joins include:

  • INNER JOIN: Returns records that have matching values in both tables
  • LEFT JOIN: Returns all records from the left table and matched records from the right table
  • RIGHT JOIN: Returns all records from the right table and matched records from the left table
Task: Join Student and Course Tables

Let's practice joining tables to see student enrollments:

  1. View the database structure:Student courses GUI
  2. Perform an INNER JOIN to find students and their courses:
    SELECT students.name, courses.course_name
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;
    Inner join students
  3. Perform a LEFT JOIN to include all students even if they haven't enrolled:
    SELECT students.name, courses.course_name
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id
LEFT JOIN courses ON enrollments.course_id = courses.id;
    Left join students
  4. Join three tables together:Three table join
When to Use Different Join Types

Choose the appropriate join type based on your needs:

  • Use INNER JOIN when you only want rows that have matches in both tables
  • Use LEFT JOIN when you want all rows from the first table, even if there's no match in the second table
  • Use RIGHT JOIN when you want all rows from the second table, even if there's no match in the first table

Lesson Summary

In this lesson, we've learned how to use JOIN statements to combine data from multiple tables. This is a crucial skill for working with relational databases where data is normalized across multiple tables.

Onwards To Lesson 9