3.2 Lab - Joining Tables

Introduction

Joining tables is an essential skill in relational database management. It allows you to combine data from multiple tables based on relationships defined by keys. In this lab, you will learn how to use different types of joins, including INNER JOIN, LEFT JOIN, and RIGHT JOIN, to retrieve meaningful data from related tables in MySQL.

---

Objectives

By the end of this lab, you will:

1. Understand how to use INNER JOIN to retrieve matching rows from multiple tables.
2. Use LEFT JOIN to retrieve all rows from one table and matching rows from another.
3. Use RIGHT JOIN to retrieve all rows from the second table and matching rows from the first.
4. Practice writing queries to combine data from related tables.

---

Lab Steps

---

Step 1: Connect to MySQL

1. Open your terminal or command prompt.

2. Log in to the MySQL server:

   mysql -u root -p
   

   • Replace root with your MySQL username.
   • Enter your password when prompted.

3. Create a new database for this lab:

   CREATE DATABASE join_lab;
   USE join_lab;
   

---

Step 2: Create and Populate Tables

1. Create the Students table:

   CREATE TABLE Students (
       StudentID INT PRIMARY KEY,
       Name VARCHAR(100)
   );
   

2. Insert data into the Students table:

   INSERT INTO Students (StudentID, Name) VALUES
   (1, 'Alice'),
   (2, 'Bob'),
   (3, 'Charlie');
   

3. Create the Courses table:

   CREATE TABLE Courses (
       CourseID INT PRIMARY KEY,
       CourseName VARCHAR(100),
       StudentID INT
   );
   

4. Insert data into the Courses table:

   INSERT INTO Courses (CourseID, CourseName, StudentID) VALUES
   (101, 'Math', 1),
   (102, 'Science', 2),
   (103, 'History', 1);
   

5. Verify the data:

   • Students table:

     SELECT * FROM Students;
     

   • Courses table:

     SELECT * FROM Courses;
     

---

Step 3: Use INNER JOIN

1. Write a query to retrieve student names and the courses they are enrolled in:

   SELECT Students.Name, Courses.CourseName
   FROM Students
   INNER JOIN Courses
   ON Students.StudentID = Courses.StudentID;
   

2. Analyze the result:

   • This query retrieves rows where there is a match in both tables based on StudentID.

Expected Output:

Name	CourseName
Alice	Math
Alice	History
Bob	Science

---

Step 4: Use LEFT JOIN

1. Write a query to retrieve all students, including those not enrolled in any courses:

   SELECT Students.Name, Courses.CourseName
   FROM Students
   LEFT JOIN Courses
   ON Students.StudentID = Courses.StudentID;
   

2. Analyze the result:

   • This query retrieves all rows from the Students table, including students without matching courses.

Expected Output:

Name	CourseName
Alice	Math
Alice	History
Bob	Science
Charlie	NULL

---

Step 5: Use RIGHT JOIN

1. Write a query to retrieve all courses, including those not assigned to any student:

   SELECT Students.Name, Courses.CourseName
   FROM Students
   RIGHT JOIN Courses
   ON Students.StudentID = Courses.StudentID;
   

2. Analyze the result:

   • This query retrieves all rows from the Courses table, including courses not linked to any student.

Expected Output:

Name	CourseName
Alice	Math
Alice	History
Bob	Science

---

Step 6: Advanced Join with Multiple Tables

1. Add an Instructors table:

   CREATE TABLE Instructors (
       InstructorID INT PRIMARY KEY,
       Name VARCHAR(100),
       CourseID INT
   );
   

2. Insert data into the Instructors table:

   INSERT INTO Instructors (InstructorID, Name, CourseID) VALUES
   (1, 'Dr. Smith', 101),
   (2, 'Dr. Jones', 102),
   (3, 'Dr. Brown', 103);
   

3. Write a query to retrieve student names, course names, and instructor names:

   SELECT Students.Name AS StudentName, Courses.CourseName, Instructors.Name AS InstructorName
   FROM Students
   INNER JOIN Courses ON Students.StudentID = Courses.StudentID
   INNER JOIN Instructors ON Courses.CourseID = Instructors.CourseID;
   

4. Analyze the result:

   • This query combines data from three tables, showing the relationship between students, courses, and instructors.

Expected Output:

StudentName	CourseName	InstructorName
Alice	Math	Dr. Smith
Alice	History	Dr. Brown
Bob	Science	Dr. Jones

---

Step 7: Exit MySQL

1. Exit the MySQL command line:

   EXIT;
   

2. Close your terminal or command prompt.

---

Summary

In this lab, you learned how to:

• Use INNER JOIN to retrieve rows with matching values in related tables.
• Use LEFT JOIN to include all rows from one table, regardless of matches in the other.
• Use RIGHT JOIN to include all rows from the second table, even if no match exists in the first.
• Perform advanced joins across multiple tables to extract meaningful relationships.

These skills are fundamental for querying relational databases and retrieving meaningful insights from connected data.