FULL OUTER JOIN Examples

Let’s look at some practical examples for a better understanding of FULL OUTFULL OUTER JOINER JOIN. First, we will create two tables called students and courses on which we will perform the FULL OUTER JOIN.

Creating the Tables:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(255),
    course_id INT
);
INSERT INTO students (student_id, student_name, course_id)
VALUES
    (1, 'Alice', 101),
    (2, 'Bob', 102),
    (3, 'Charlie', NULL),
    (4, 'David', 103);
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL
);
INSERT INTO courses (course_id, course_name)
VALUES
    (101, 'Mathematics'),
    (102, 'Physics'),
    (103, 'Chemistry'),
    (104, 'Computer Science');

Output:

The students table will look like this:

The courses  table will look like this:

Example 1: Enrolled Students and Courses

Consider the situation where we want to fetch a list of all students and their respective courses, including those without assigned courses.

Query:

SELECT students.student_id, students.student_name, COALESCE(courses.course_name, 'No Course') 
AS course_name
FROM students
LEFT JOIN courses ON students.course_id = courses.course_id
UNION
SELECT students.student_id, students.student_name, courses.course_name
FROM students
RIGHT JOIN courses ON students.course_id = courses.course_id;

Output:

Explanation: In this result, we see all students and their courses. Students without courses and courses without students are also included.

Example 2: Enrolled Courses and Students

Suppose We want to see all courses and their enrolled students.

Query:

SELECT courses.course_id, courses.course_name, COALESCE(GROUP_CONCAT(students.student_name), 
'No Students') AS enrolled_students
FROM courses
LEFT JOIN students ON courses.course_id = students.course_id
GROUP BY courses.course_id, courses.course_name
UNION
SELECT courses.course_id, courses.course_name, GROUP_CONCAT(students.student_name) AS enrolled_students
FROM courses
RIGHT JOIN students ON courses.course_id = students.course_id
GROUP BY courses.course_id, courses.course_name;

Output:

Explanation: This result provides a list of all courses and the students enrolled in each course. Courses without students and students not enrolled in any course are also included.

Example 3: Sorting Students and Courses Alphabetically

Let’s reconsider Example 1 to include an ORDER BY clause and sort the results alphabetically by student name and then by course name.

Query:

SELECT students.student_id, students.student_name, COALESCE(courses.course_name, 'No Course') 
AS course_name
FROM students
LEFT JOIN courses ON students.course_id = courses.course_id
UNION
SELECT students.student_id, students.student_name, courses.course_name
FROM students
RIGHT JOIN courses ON students.course_id = courses.course_id
ORDER BY student_name, course_name;

Output:

Explanation: In this result, the rows are sorted alphabetically by student name and then by course name.

The FULL OUTER JOIN or FULL JOIN in MySQL is a powerful technique to fetch all rows from both tables, including matched and unmatched records. In this article, we will explore how to use FULL OUTER JOIN with practical examples and MySQL queries. Although MySQL doesn’t natively support FULL OUTER JOIN, we can achieve the same result using a combination of LEFT JOIN, RIGHT JOIN, and the UNION operator.