Learning of SQL Day 21

 

Day 21: OUTER JOIN Operations (LEFT, RIGHT, FULL OUTER)

Introduction:

• The OUTER JOIN clause is used to combine rows from two or more tables and includes rows that do not have matching values in both tables. The types of OUTER JOINs are LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Key Concepts:

• LEFT JOIN: Returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned.

• RIGHT JOIN: Returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned.

• FULL OUTER JOIN: Returns all rows when there is a match in either left or right table. If no match is found, NULL values are returned.

SQL Commands and Examples:

1. LEFT JOIN:

   • Combines rows from the left table with matching rows from the right table.

sql

-- Select all students and their courses, include students without a course
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
LEFT JOIN Courses ON Students.CourseID = Courses.CourseID;

2. RIGHT JOIN:

   • Combines rows from the right table with matching rows from the left table.

sql

-- Select all courses and their students, include courses without students
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
RIGHT JOIN Courses ON Students.CourseID = Courses.CourseID;

3. FULL OUTER JOIN:

   • Combines rows from both tables, including non-matching rows.

sql

-- Select all students and all courses, include students without a course and courses without students
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
FULL OUTER JOIN Courses ON Students.CourseID = Courses.CourseID;

Practice Exercise:

1. Use the Students and Courses tables.

2. Write a query to left join the Students and Courses tables and select StudentID, FirstName, and CourseName.

3. Write a query to right join the Students and Courses tables and select StudentID, FirstName, and CourseName.

4. Write a query to full outer join the Students and Courses tables and select StudentID, FirstName, and CourseName.

sql

-- Left join Students and Courses tables and select StudentID, FirstName, and CourseName
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
LEFT JOIN Courses ON Students.CourseID = Courses.CourseID;

-- Right join Students and Courses tables and select StudentID, FirstName, and CourseName
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
RIGHT JOIN Courses ON Students.CourseID = Courses.CourseID;

-- Full outer join Students and Courses tables and select StudentID, FirstName, and CourseName
SELECT Students.StudentID, Students.FirstName, Courses.CourseName
FROM Students
FULL OUTER JOIN Courses ON Students.CourseID = Courses.CourseID;

Understanding OUTER JOIN operations is crucial for combining data from multiple tables and handling cases where some rows do not have matching values.