How to Use Full Outer Join in MySQL

The FULL OUTER JOIN or FULL JOIN is a useful technique in MySQL that allows us to fetch all rows from both tables when there is a match in the records.

In this article, we will learn about the FULL OUTER JOIN through practical examples and MySQL queries.

MySQL FULL OUTER JOIN

The FULL OUTER JOIN in MySQL returns all rows of both tables involved in the JOIN operation in the result set. If there is no match for a particular row based on the specified condition, the result will include NULL values for columns from the table that do not have a match.

This makes FULL OUTER JOIN useful when we want to include unmatched rows from both tables.

Note: MySQL does not explicitly support a FULL OUTER JOIN. Instead, we can achieve it by combining a LEFT JOIN, a RIGHT JOIN, and a UNION operator. You can use FULL OUTER JOIN in SQL using FULL OUTER JOIN keyword.

Syntax

SELECT *FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name
UNION
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

• The LEFT JOIN fetches all rows from the left table and the matched rows from the right table.
• The RIGHT JOIN fetches all rows from the right table and the matched rows from the left table.
• The UNION operator combines the results of the two SELECT statements.

FULL OUTER JOIN Examples

Let’s look at some practical examples for a better understanding of FULL OUTER JOIN.

First, we will create 2 tables called students and courses on which we will perform the FULL OUTER JOIN.

MySQL

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:

Students Table

The courses  table will look like this:

Courses Table

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:

MySQL

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:

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:

MySQL

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:

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:

MySQL

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:

Output

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

Conclusion

MySQL FULL OUTER JOIN returns data from left and right tables that satisfy the given condition. In MySQL, you can not directly use the FULL OUTER JOIN or FULL JOIN, so we use a combination of LEFT JOIN or RIGHT JOIN and UNION operator.

This tutorial explained how to use these combinations to perform FULL JOIN in MySQL. With the practical example, you can practice FULL OUTER JOIN queries in MySQL and combine records to find insights.