SQL : Using JOINs

Objectives:

• Understand different types of JOINs: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
• Learn how to write SQL queries using JOINs to combine data from multiple tables
• Practice writing queries with JOIN conditions

---

1. Introduction to JOINs (5 minutes)

Definition:

• A JOIN clause is used to combine rows from two or more tables based on a related column.

Types of JOINs:

• INNER JOIN: Returns only the rows that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.
• FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in either table. If there is no match, NULL values are returned.

---

2. INNER JOIN (10 minutes)

Syntax Overview:

• Combines rows from two tables where the join condition is met.

  SELECT columns
  FROM table1
  INNER JOIN table2
  ON table1.common_column = table2.common_column;

Example:

SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

Exercise:

• Write SQL queries to:

1. Select StudentID and FirstName from Students and CourseName from Courses for students enrolled in ‘Math’ course.

---

3. LEFT JOIN (10 minutes)

Syntax Overview:

• Returns all rows from the left table and the matched rows from the right table. Rows without a match will have NULL values.

  SELECT columns
  FROM table1
  LEFT JOIN table2
  ON table1.common_column = table2.common_column;

Example:

SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
LEFT JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

Exercise:

• Write SQL queries to:

1. List all students and their enrolled courses, including students who are not enrolled in any course.

---

4. RIGHT JOIN (10 minutes)

Syntax Overview:

• Returns all rows from the right table and the matched rows from the left table. Rows without a match will have NULL values.

  SELECT columns
  FROM table1
  RIGHT JOIN table2
  ON table1.common_column = table2.common_column;

Example:

SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
RIGHT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
RIGHT JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

Exercise:

• Write SQL queries to:

1. Show all courses and the students enrolled in them, including courses with no students.

---

5. FULL JOIN (10 minutes)

Syntax Overview:

• Returns all rows when there is a match in either table. If there is no match, NULL values are returned.

  SELECT columns
  FROM table1
  FULL JOIN table2
  ON table1.common_column = table2.common_column;

Example:

SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
FULL JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
FULL JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

Exercise:

• Write SQL queries to:

1. Retrieve all students and courses, showing students with no courses and courses with no students.

---

6. Practical Exercise: Using JOINs (10 minutes)

• Task:
• Use the Students, Courses, and Enrollments tables.
• Insert sample data into Courses and Enrollments tables:

  INSERT INTO Courses (CourseID, CourseName)
  VALUES 
      (1, 'Math'),
      (2, 'Science'),
      (3, 'History');

  INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID)
  VALUES 
      (1, 1, 1),
      (2, 2, 2),
      (3, 3, 1),
      (4, 4, 3);

• Exercises:

1. Retrieve all students and the courses they are enrolled in using INNER JOIN.
2. List all courses and students who are enrolled in them using LEFT JOIN.
3. Show all enrollments, including students and courses, using RIGHT JOIN.
4. Get a complete list of students and courses, using FULL JOIN.

---

Exercises

1. INNER JOIN:

• Exercise: Write SQL queries to join Students and Enrollments tables to show students and their courses.

1. LEFT JOIN:

• Exercise: Write SQL queries to list all students and their courses, including those without any enrollments.

1. RIGHT JOIN:

• Exercise: Write SQL queries to show all courses and the students enrolled in them, including courses with no students.

1. FULL JOIN:

• Exercise: Write SQL queries to retrieve all students and courses, showing students with no courses and courses with no students.

---

Homework:

• Practice Questions:
• Write SQL queries to:
  • List all students and the courses they are enrolled in, using various JOIN types.
  • Show all enrollments, including students and courses, with appropriate JOIN clauses.

---