[Basic Grammar] SQL FULL OUTER JOIN

The FULL OUTER results in all records when there is a match in left or right table records. 

 

• FULL OUTER JOIN Syntax 

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

 

Example 

The following SQL statement selects all customers, and all orders:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Exercises 

Write an SQL query to retrieve a list of all students and their enrolled courses. Use a FULL OUTER JOIN to ensure that all students and all courses are included in the result, even if some students are not enrolled in any courses and some courses have no enrolled students.

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